OK, as I see things:
* SQL is allegedly a declarative language.
* Oracle is touted as the most performant, mature, ......, product in the
marketplace.
* The Oracle CBO makes execution plan/path decisions based on the 'cost' of a
number different execution path candidates.
* The Oracle optimiser can't even rewrite the most simple of algebraic
expressions to result in a query with a low cost execution path.
* My 9 year old daughter has the skills to rewrite simple algebraic expressions.
And yes, I have my grumpy old man hat on, and yes I do understand why the
'cost' of the two following queries is so different, and yes I have read many
of manuals, but none of this means that the following behaviour (see below)
still isn't crap. I am quite confident that even a mediocre developer would be
able to rewrite some of the optimiser code to accommodate for this type of
simple algebra, with the ultimate goal of ensuring that an indexed column was
used when it made sense.
~
Mike
http://www.strychnine.co.uk ;<http://www.strychnine.co.uk/>
SQL> -- Table name anonymised but largeTable is large and partitioned on
partDate
SQL> -- largeTab_id is the PK
SQL>
-------------------------------------------------------------------------------
SQL>
SQL>
SQL> set linesize 132
SQL> set wrap off
SQL> set trunc off
SQL> set tab off
SQL>
SQL> explain plan for
2 select *
3 from largeTable
4 where largeTab_id+1 = 13536978456
5 and partDate = date '2014-12-2';
Explained.
SQL> select *
2 from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3163595393
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52841 | 6347K| 12712 (3)|
00:02:33 | | |
| 1 | PARTITION RANGE SINGLE| | 52841 | 6347K| 12712 (3)|
00:02:33 | 1 | 1 |
| 2 | PARTITION LIST ALL | | 52841 | 6347K| 12712 (3)|
00:02:33 | 1 | 6 |
|* 3 | TABLE ACCESS FULL | largeTable | 52841 | 6347K| 12712 (3)|
00:02:33 | 1 | 6 |
-----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("largeTab_id"+1=13536978456 AND "partDate"=TO_DATE(' 2014-12-02
00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
16 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> explain plan for
2 select *
3 from largeTable
4 where largeTab_id = 13536978456-1
5 and partDate = date '2014-12-2';
Explained.
SQL> select *
2 from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 1525276650
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 123 |
24 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 123 |
24 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST ALL | | 1 | 123 |
24 (0)| 00:00:01 | 1 | 6 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| largeTable | 1 | 123 |
24 (0)| 00:00:01 | 1 | 6 |
|* 4 | INDEX RANGE SCAN | largeTable_IX01 | 1 | |
23 (0)| 00:00:01 | 1 | 6 |
-----------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("partDate"=TO_DATE(' 2014-12-02 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
4 - access("largeTab_id"=13536978455)
17 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
SQL>
SQL>
Attachment:
signature.asc
Description: Message signed with OpenPGP using GPGMail
