How to find out fast the partitioning keys in tables or indexes?

The key in this question is the word "fast"; there are many ways to find the partitioning key for a table or an index: using dbms_metadata.get_ddl, export/import using indexfile, ussing expdp and impdp, but the fastest way is to query the views below:

DBA_PART_KEY_COLUMNS
DBA_SUBPART_KEY_COLUMNS
USER_PART_KEY_COLUMNS
USER_SUBPART_KEY_COLUMNS
ALL_PART_KEY_COLUMNS
ALL_SUBPART_KEY_COLUMNS

They provide info for subpartitions also.

Example:

SQL> select * from DBA_PART_KEY_COLUMNS

  2  where owner='CUSTO'

  3   and NAME='TRB1_AUDIT_SUB_LOG'

  4  order by COLUMN_POSITION;

OWNER                          NAME                           OBJEC COLUMN_NAME                    COLUMN_POSITION

------------------------------ ------------------------------ ----- ------------------------------ ---------------

CUSTO                       MY_AUDIT_LOG             TABLE PERIOD_KEY                                   1

CUSTO                       MY_AUDIT_LOG             TABLE SOURCE_MEMBER_ID                             2

CUSTO                       MY_AUDIT_LOG             TABLE SUB_APPL_ID                                  3

CUSTO                       MY_AUDIT_LOG             TABLE PROCESS_TYPE_IND                             4

Oracle Optimizer and the evergreen need for hints

The oracle optimizer is a software, trying to generate the best execution plan for a query, with the information it has. So in theory, if we provide it the most info we can, it should always be able to generate the best execution plan. But does it? Not always.
Even if we provide the optimizer fresh statistics of all the tables involved, there is still a risk that the optimizer
will take poor decisions, since it does not know how many rows will be returned by a join and it has to estimate the
result. Sometimes, wrongly estimating that a 2 table join will return a very small amount of rows, and this result being
used as the driving table in a Nested Loop join, can seriously hinder performance.

 The solution in this particular case is to use the use_hash hint, to avoid the NL altogether.

GATHER_PLAN_STATISTICS hint and dbms_xplan

In order to get detailed information about how many rows are being returned in each step of the execution plan, the hint GATHER_PLAN_STATISTICS comes in very handy, see the simple example below:

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'FRANK';

 You actually have to run the above query, to be able to see that run stats.


SET LINE 200 pages 80

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS                |         |      1 |        |      1 |00:00:00.01 |       9 |
|   2 |   NESTED LOOPS               |         |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |    TABLE ACCESS FULL         | EMP     |      1 |      1 |      1 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

The output above shows the estimated rows returned (E-Rows), the actual rows returned (A-Rows) and the actual time (A-Time) spent during each step of the execution.