Why are no alternatives generated with different table orders in FROM clause when optimize preference is custom set for ORDERED, even when all quota are set to 1000.
For example:
Original SQL
SELECT
X
FROM TABLE1 T1, TABLE2 T2, TABLE3 T3
WHERE
T1.KEYA = 1A
AND T1.KEYB = 1B
AND T1.KEYC = 1C
AND T3.KEY = 3
AND T1.KEY = T2.KEY
AND T2.KEY = T3.KEY
In options, disable all hints except ORDERED. Change quotas to 1000. The only alternative generated with ORDERED will be with tables listed in the original FROM clause order. It fails to generate alternatives for other table orders, e.g. FROM TABLE3 T3, TABLE1 T1, TABLE2 T2.
i.e.
Original:
SELECT X
FROM TABLE_1 T1,
TABLE_2 T2,
TABLE_3 T3
WHERE T1.KEYA = 1A
AND T1.KEYB = 1B
AND T1.KEYC = 1C
AND T3.KEYR = 3
AND T1.KEYB = T2.KEYY
AND T2.KEYY = T3.KEYS
Originals Plan
1 Every row in the table CHAD.TABLE_3 is read.
2 The rows were sorted to support the join at step 1.
3 Every row in the table CHAD.TABLE_2 is read.
4 The rows were sorted to support the join at step 3.
5 Join the sorted results sets provided from steps 2, 4.
6 Every row in the table CHAD.TABLE_1 is read.
7 The rows were sorted to support the join at step 6.
8 Join the sorted results sets provided from steps 5, 7.
9 Rows were returned by the SELECT statement.
Alt 1:
SELECT /*+ ORDERED */ X
FROM TABLE_1 T1,
TABLE_2 T2,
TABLE_3 T3
WHERE T1.KEYA = 1A
AND T1.KEYB = 1B
AND T1.KEYC = 1C
AND T3.KEYR = 3
AND T1.KEYB = T2.KEYY
AND T2.KEYY = T3.KEYS
Alt 1s Plan:
1 Every row in the table CHAD.TABLE_1 is read.
2 Every row in the table CHAD.TABLE_2 is read.
3 The result sets from steps 1, 2 were joined (hash).
4 Every row in the table CHAD.TABLE_3 is read.
5 The result sets from steps 3, 4 were joined (hash).
6 Rows we
You need to be signed in and under a current maintenance contract to view premium knowledge articles.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center