SQL & PL/SQL

SQL> CREATE TABLE test_1
  2  AS
  3  SELECT  *
  4  FROM    all_objects
  5  ;

Tabelle wurde angelegt.

SQL> CREATE TABLE test_2
  2  AS
  3  SELECT  DISTINCT
  4          object_type
  5  FROM    test_1
  6  ;

Tabelle wurde angelegt.

SQL> CREATE INDEX idx_1
  2  ON test_1
  3  (   object_type,
  4      created DESC
  5  );

Index wurde angelegt.

SQL> CREATE UNIQUE INDEX idx_2
  2  ON test_2
  3  (   object_type
  4  );

Index wurde angelegt.

SQL> BEGIN
  2      dbms_stats.gather_table_stats( ownname => USER,tabname => 'TEST_1',cascade => TRUE);
  3      dbms_stats.gather_table_stats( ownname => USER,tabname => 'TEST_2',cascade => TRUE);
  4  END;
  5  /

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> SET AUTOTRACE TRACEONLY

SQL> SELECT  created
  2  FROM    test_1
  3  WHERE   object_type = 'TABLE'
  4  ORDER BY 1 DESC
  5  ;

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2368 Bytes=42624)
   1    0   INDEX (RANGE SCAN) OF 'IDX_1' (INDEX) (Cost=3 Card=2368 Bytes=42624)

Ok, here the DESC Order of the created column in the Index will be used. We have no SORT Operation in the execution plan.

But why can't the CBO use the DESC order of the column in this Query? Here we have a SORT operation:

SQL> SELECT  created
  2  FROM    test_1
  3  WHERE   object_type = ( SELECT object_type FROM test_2 WHERE object_type = 'TABLE' )
  4  ORDER BY 1 DESC
  5  ;

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=2368 Bytes=42624)
   1    0   SORT (ORDER BY) (Cost=5 Card=2368 Bytes=42624)
   2    1     INDEX (RANGE SCAN) OF 'IDX_1' (INDEX) (Cost=3 Card=2368 Bytes=42624)
   3    2       INDEX (UNIQUE SCAN) OF 'IDX_2' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=9)

Edited by: Rumburak on Jul 28, 2009 4:36 PM