Strangeness of optimization (usage of UID)

SQL Optimizer SQL Optimizer for Oracle
1

Hi. I use Quest SQL Optimizer 8.8.1 for Oracle. I have source query to optimize:

SELECT A.COLUMNA1, A.COLUMNX XA, B.COLUMNB1, B.COLUMNX BX
FROM TABLEA A, TABLEB B
WHERE A.COLUMNX=B.COLUMNX
AND A.COLUMNA1=5
AND B.ColumnB1 BETWEEN 1 AND 10

and it’s execution plan:

Plan
4 SELECT STATEMENT ALL_ROWS
Cost: 2 039 Bytes: 2 177 071 E-Rows (Cardinality): 128 063 CPU Cost: 567 685 474 IO Cost: 2 013 Time: 00:00:25.00
3 HASH JOIN
Access: “A”.“COLUMNX”=“B”.“COLUMNX”
Projection: (#keys=1) “A”.“COLUMNX”[NUMBER,22], “B”.“COLUMNX”[NUMBER,22], “A”.“COLUMNA1”[NUMBER,22], “B”.“COLUMNB1”[NUMBER,22]
Cost: 2 039 Bytes: 2 177 071 E-Rows (Cardinality): 128 063 CPU Cost: 567 685 474 IO Cost: 2 013 Time: 00:00:25.00
1 TABLE ACCESS FULL SYSTEM.TABLEA [Analyzed]
Filter: “A”.“COLUMNA1”=5
Projection: “A”.“COLUMNA1”[NUMBER,22], “A”.“COLUMNX”[NUMBER,22]
Cost: 800 Bytes: 866 400 E-Rows (Cardinality): 108 300 CPU Cost: 223 429 390 IO Cost: 790 Time: 00:00:10.00
2 TABLE ACCESS FULL SYSTEM.TABLEB [Analyzed]
Filter: “B”.“COLUMNB1”<=10 AND “B”.“COLUMNB1”>=1
Projection: “B”.“COLUMNB1”[NUMBER,22], “B”.“COLUMNX”[NUMBER,22]
Cost: 801 Bytes: 2 991 150 E-Rows (Cardinality): 332 350 CPU Cost: 246 027 890 IO Cost: 790 Time: 00:00:10.00

after generation of queries in Quest SQL Optimizer I got next query with lower cost:

SELECT A.COLUMNA1,
A.COLUMNX XA,
B.COLUMNB1,
B.COLUMNX BX
FROM TABLEA A,
TABLEB B
WHERE A.COLUMNX = B.COLUMNX
AND A.COLUMNA1 = 5
AND B.COLUMNB1 >= 1 + UID * 0
AND B.ColumnB1 <= 10

and optimized execution plan:

Plan
5 SELECT STATEMENT ALL_ROWS
Cost: 1 608 Bytes: 300 220 E-Rows (Cardinality): 17 660 CPU Cost: 603 718 596 IO Cost: 1 580 Time: 00:00:20.00
4 FILTER
Filter: 1+UID@!*0<=10
Projection: “B”.“COLUMNX”[NUMBER,22], “A”.“COLUMNX”[NUMBER,22], “B”.“COLUMNB1”[NUMBER,22], “A”.“COLUMNA1”[NUMBER,22]
3 HASH JOIN
Access: “A”.“COLUMNX”=“B”.“COLUMNX”
Projection: (#keys=1) “B”.“COLUMNX”[NUMBER,22], “A”.“COLUMNX”[NUMBER,22], “B”.“COLUMNB1”[NUMBER,22], “A”.“COLUMNA1”[NUMBER,22]
Cost: 1 608 Bytes: 300 220 E-Rows (Cardinality): 17 660 CPU Cost: 603 718 596 IO Cost: 1 580 Time: 00:00:20.00
1 TABLE ACCESS FULL SYSTEM.TABLEB [Analyzed]
Filter: “B”.“COLUMNB1”<=10 AND “B”.“COLUMNB1”>=1+UID@!*0
Projection: “B”.“COLUMNB1”[NUMBER,22], “B”.“COLUMNX”[NUMBER,22]
Cost: 806 Bytes: 149 562 E-Rows (Cardinality): 16 618 CPU Cost: 356 035 750 IO Cost: 790 Time: 00:00:10.00
2 TABLE ACCESS FULL SYSTEM.TABLEA [Analyzed]
Filter: “A”.“COLUMNA1”=5
Projection: “A”.“COLUMNA1”[NUMBER,22], “A”.“COLUMNX”[NUMBER,22]
Cost: 800 Bytes: 866 400 E-Rows (Cardinality): 108 300 CPU Cost: 223 429 390 IO Cost: 790 Time: 00:00:10.00

Why do I get a prize from the filter “1+UID@!*0<=10”, and whether such a query is optimal for other data? Will I get better time of execution for optimized query?

2

The “UID*0” in condition “B.COLUMNB1 >= 1 + UID * 0” is used to hide value “1”, so Oracle cannot see this value during optimization, since UID is unknown until execution. In certain conditions this dummy operation can help Oracle to select better plan. Don’t use this dummy operation until you have tested run the SQL and sure it is a better plan. For any alternative generated by our Dell SQL Optimizer must be tested run, don’t just rely on Oracle cost to select the best alternative, since Oracle cost is not accurate especially for problematic SQL.

Richard

3

Mike, you better gather statistics on table B…