RE: PL.SQL question- Can anyone explain why the Oracle SQL engine is
- From: "Good, John" <John.Good@xxxxxxxxxxxxxxxxxxxxx>
- To: <programmingblind@xxxxxxxxxxxxx>
- Date: Thu, 15 May 2008 14:27:24 -0400
Dear Peter,
I removed the truncate and code the following select which as you can
from the explain plan worked quite nicely.
SELECT /*+ INDEX(IT_TBL_T_TAX_TXN_DETAILS IT_NDX_S_DATE_POSTED) */
IT_TBL_T_TAX_TXN_DETAILS.TRANSACTION_NO
FROM IT_TBL_T_TAX_TXN_DETAILS
WHERE IT_TBL_T_TAX_TXN_DETAILS.DATE_POSTED >= '20-DEC-2006'
AND IT_TBL_T_TAX_TXN_DETAILS.DATE_POSTED < '21-DEC-2006'
Plan
1 One or more rows were retrieved using index
PITSDBA.IT_NDX_S_DATE_POSTED . The index was scanned in ascending
order..
2 Rows from table PITSDBA.IT_TBL_T_TAX_TXN_DETAILS were accessed using
rowid got from an index.
3 Rows were returned by the SELECT statement.
Thank you for your help. I'll keep in mind the Option of a
truncated index for the future. Seems to me, in most cases that is what
most applications would need.
John
-----Original Message-----
From: programmingblind-bounce@xxxxxxxxxxxxx
[mailto:programmingblind-bounce@xxxxxxxxxxxxx] On Behalf Of Peter
Quaiattini
Sent: Thursday, May 15, 2008 1:40 PM
To: programmingblind@xxxxxxxxxxxxx
Subject: Re: PL.SQL question- Can anyone explain why the Oracle SQL
engine is
Alternatively, if the TRUNC function is required, then you could create
a function-based index.
Peter Quaiattini
BITS OPS - Data Hosting Services
Canadian Pacific Railway
(403) 319-6579
peter_quaiattini@xxxxxx
-----Original Message-----
From: FreeLists Mailing List Manager [mailto:ecartis@xxxxxxxxxxxxx]
Date: Wed, 14 May 2008 10:35:55 +0100
From: Jim Dunleavy <jim.dunleavy@xxxxxx>
Subject: Re: PL.SQL question- Can anyone explain why the Oracle SQL
engine is
Hi John,
Applying functions such as "trunc" to indexed columns prevents indexed
access.
Try rewriting the condition without using the trunc function.
You probable won't even need to include the index hint then.
e.g.:
WHERE IT_TBL_T_TAX_TXN_DETAILS.DATE_POSTED >= '20-DEC-2007'
AND IT_TBL_T_TAX_TXN_DETAILS.DATE_POSTED < '21-DEC-2007'
HTH
--Jim
----- Original Message -----
From: Good, John
To: programmingblind@xxxxxxxxxxxxx
Sent: Tuesday, May 13, 2008 6:54 PM
Subject: PL.SQL question- Can anyone explain why the Oracle SQL engine
is doingthis?
Hello all,
I am confused on why the below is occurring. I have executed an
explain plan on the below select and am confused why it did not use the
indicated index designated in the hint clause of the select. The table
definition and it's indexes for the table are listed below the explain
plan entry.
Any help is greatly appreciated.
Thanks, John
SELECT /*+ INDEX(IT_TBL_T_TAX_TXN_DETAILS IT_NDX_S_DATE_POSTED) */
IT_TBL_T_TAX_TXN_DETAILS.TRANSACTION_NO
FROM IT_TBL_T_TAX_TXN_DETAILS
WHERE TRUNC(IT_TBL_T_TAX_TXN_DETAILS.DATE_POSTED) = '20-DEC-2007'
Plan
1 Every row in the table PITSDBA.IT_TBL_T_TAX_TXN_DETAILS is read.
2 Rows were returned by the SELECT statement.
Table definition with it's create index statements:
CREATE TABLE IT_TBL_T_TAX_TXN_DETAILS (
TRANSACTION_NO CHAR(12) NOT NULL,
SEQUENCE_NO CHAR(2) NOT NULL,
ACCT_NO CHAR(9) NOT NULL,
TAX_TYPE VARCHAR2(6) NOT NULL,
FORM_TYPE VARCHAR2(6),
TAX_YR NUMBER(4) NOT NULL,
TAX_PERIOD VARCHAR2(2),
TRANSACTION_TYPE VARCHAR2(6) NOT NULL,
SOURCE_OF_TRANSACTION VARCHAR2(6),
SOURCE_OF_CHARGES VARCHAR2(6),
YEAR_END NUMBER(2),
CODE_TYPE NUMBER(1),
NO_P_I CHAR(1) NOT NULL,
AMT_RCVD NUMBER(12,2) DEFAULT 0,
CITY_AMT NUMBER(12,2) DEFAULT 0,
SCHOOL_AMT NUMBER(12,2) DEFAULT 0,
TYPE_OF_PAYMENT VARCHAR2(6),
TYPE_OF_RETURN CHAR(1),
CITY_LIABILITY_ASSESSED NUMBER(12,2) DEFAULT 0,
SCHOOL_LIABILITY_ASSESSED NUMBER(12,2) DEFAULT 0,
NON_PA_LIABILITY_ASSESSED NUMBER(12,2) DEFAULT 0,
CITY_INTEREST_ASSESSED NUMBER(12,2) DEFAULT 0,
SCHOOL_INTEREST_ASSESSED NUMBER(12,2) DEFAULT 0,
NON_PA_INTEREST_ASSESSED NUMBER(12,2) DEFAULT 0,
CITY_PENALTY_ASSESSED NUMBER(12,2) DEFAULT 0,
SCHOOL_PENALTY_ASSESSED NUMBER(12,2) DEFAULT 0,
NON_PA_PENALTY_ASESSED NUMBER(12,2) DEFAULT 0,
CITY_ESTIMATED_P_I_ASSESSED NUMBER(12,2) DEFAULT 0,
SCHOOL_ESTIMATED_P_I_ASSESSED NUMBER(12,2) DEFAULT 0,
SERVICE_CHARGE_ASSESSED NUMBER(12,2) DEFAULT 0,
CITY_LIABILITY_PAID NUMBER(12,2) DEFAULT 0,
SCHOOL_LIABILITY_PAID NUMBER(12,2) DEFAULT 0,
NON_PA_LIABILITY_PAID NUMBER(12,2) DEFAULT 0,
CITY_INTEREST_PAID NUMBER(12,2) DEFAULT 0,
SCHOOL_INTEREST_PAID NUMBER(12,2) DEFAULT 0,
NON_PA_INTEREST_PAID NUMBER(12,2) DEFAULT 0,
CITY_PENALTY_PAID NUMBER(12,2) DEFAULT 0,
SCHOOL_PENALTY_PAID NUMBER(12,2) DEFAULT 0,
NON_PA_PENALTY_PAID NUMBER(12,2) DEFAULT 0,
CITY_ESTIMATED_P_I_PAID NUMBER(12,2) DEFAULT 0,
SCHOOL_ESTIMATED_P_I_PAID NUMBER(12,2) DEFAULT 0,
SERVICE_CHARGE_PAID NUMBER(12,2) DEFAULT 0,
CITY_ESTIMATED_TAX NUMBER(12,2) DEFAULT 0,
SCHOOL_ESTIMATED_TAX NUMBER(12,2) DEFAULT 0,
CITY_ESTIMATED_TAX_PAID NUMBER(12,2) DEFAULT 0,
SCHOOL_ESTIMATED_TAX_PAID NUMBER(12,2) DEFAULT 0,
CITY_ESTIMATED_INTEREST_PAID NUMBER(12,2) DEFAULT 0,
SCHOOL_ESTIMATED_INTEREST_PAID NUMBER(12,2) DEFAULT 0,
CITY_ESTIMATED_PENALTY_PAID NUMBER(12,2) DEFAULT 0,
SCHOOL_ESTIMATED_PENALTY_PAID NUMBER(12,2) DEFAULT 0,
CITY_REFUND_AMT NUMBER(12,2) DEFAULT 0,
SCHOOL_REFUND_AMT NUMBER(12,2) DEFAULT 0,
CITY_CREDIT NUMBER(12,2) DEFAULT 0,
SCHOOL_CREDIT NUMBER(12,2) DEFAULT 0,
REFUND_AMOUNT_REQUESTED NUMBER(12,2) DEFAULT 0,
CREDIT_AMOUNT_REQUESTED NUMBER(12,2) DEFAULT 0,
UNPAID_CITY_CREDIT NUMBER(12,2) DEFAULT 0,
UNPAID_SCHOOL_CREDIT NUMBER(12,2) DEFAULT 0,
GROSS_AT_REVENUE NUMBER(12,2) DEFAULT 0,
BATCH_NO NUMBER(5),
ROLLBACK_FLAG CHAR(1),
DATE_DEPOSITED DATE,
DATE_POSTMARKED DATE NOT NULL,
STATUS_TXN CHAR(1) NOT NULL,
SCHEDULE_FLAG CHAR(1),
BACKCALC_FLAG CHAR(1),
WAIVE_FLAG CHAR(1),
COMPLIANCE_FLAG VARCHAR2(6),
RECLASSIFY_FLAG CHAR(1),
FLAG_1099G CHAR(1),
CURRENT_CITY_CREDIT NUMBER(12,2) DEFAULT 0,
CURRENT_SCHOOL_CREDIT NUMBER(12,2) DEFAULT 0,
CREATED_BY VARCHAR2(8) NOT NULL,
DATE_CREATED DATE NOT NULL,
MODIFIED_BY VARCHAR2(8),
DATE_MODIFIED DATE,
POSTED_BY CHAR(8),
DATE_POSTED DATE,
CITY_ROLLBACK_AMT NUMBER(12,2) DEFAULT 0,
SCHOOL_ROLLBACK_AMT NUMBER(12,2) DEFAULT 0,
OT_AMT_USED NUMBER(12,2) DEFAULT 0,
MOVED_FROM_FLAG CHAR(1),
AMOUNT_MOVED NUMBER(12,2) DEFAULT 0,
CITY_SUMMARY_BALANCE NUMBER(12,2) DEFAULT 0,
SCHOOL_SUMMARY_BALANCE NUMBER(12,2) DEFAULT 0,
CONSTRAINT PK_IT_TBL_T_TAX_TXN_DETAILS
PRIMARY KEY ( TRANSACTION_NO, SEQUENCE_NO )
USING INDEX PCTFREE 10
STORAGE(INITIAL 51200 NEXT 51200 PCTINCREASE 1 )
TABLESPACE USERS_INDEX)
TABLESPACE USER_DATA PCTFREE 10
STORAGE(INITIAL 51200 NEXT 57344 PCTINCREASE 1 )
PARALLEL (DEGREE 1 INSTANCES 1) NOCACHE;
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_TRANSACTION_TYPE_IT_TBL_T CHECK ( transaction_type in
('CRD','WAIV','ABAT','REF','BP','MT','ISP','AT','PT','NP5','WT-4','PGH-4
0','OT-3','OT-1','WTD','WT-1','WTE-3','BILL','NSF','VCHK','VOID','FCRT',
'TCRT','FRCL','TRCL','ML') );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_STATUS_TXN_IT_TBL_T CHECK (STATUS_TXN IN ('H','P') );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_BACKCALC_FLAG_IT_TBL_T CHECK ( backcalc_flag is null or
(backcalc_flag in ('Y','N')) );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_MOVED_FROM_FLAG CHECK (MOVED_FROM_FLAG IS NULL OR MOVED_FROM_FLAG
IN ('C','S') );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_1099G_FLAG_IT_TBL_T CHECK (FLAG_1099G IS NULL OR FLAG_1099G IN
('M','I','C') );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_RECLASSIFY_FLAG_IT_TBL_T CHECK ( reclassify_flag is
null or reclassify_flag = 'Y' );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_CODE_TYPE_IT_TBL_TXN_DTL CHECK (code_type is null or (code_type in
(1,2,3,4,5)) );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_NO_P_I_IT_TBL_T CHECK ( no_p_i in ('Y','N') );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_TYPE_OF_RETURN_IT_TBL_T CHECK (type_of_return is null or
(type_of_return in ('O','A','S','J')) );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_ROLLBACK_FLAG_IT_TBL_T1 CHECK ( rollback_flag is null
or (rollback_flag in ('Y')) );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_SCHEDULE_FLAG_IT_TBL_T CHECK ( schedule_flag is null or
(schedule_flag in ('Y','N')) );
ALTER TABLE IT_TBL_T_TAX_TXN_DETAILS ADD CONSTRAINT
CKC_WAIVE_FLAG_IT_TBL_T CHECK ( waive_flag is null or
(waive_flag in ('Y','N')) );
CREATE INDEX IT_NDX_S_ACCT_NO_FK20 ON
IT_TBL_T_TAX_TXN_DETAILS(ACCT_NO)
TABLESPACE USERS_INDEX PCTFREE 10
STORAGE(INITIAL 51200 NEXT 51200 PCTINCREASE 1 ) ;
CREATE INDEX IT_NDX_S_DATE_POSTED ON
IT_TBL_T_TAX_TXN_DETAILS(DATE_POSTED)
TABLESPACE USERS_INDEX PCTFREE 10
STORAGE(INITIAL 51200 NEXT 51200 PCTINCREASE 1 ) ;
CREATE INDEX IT_NDX_S_FORM_TYPE_FK4 ON
IT_TBL_T_TAX_TXN_DETAILS(FORM_TYPE)
TABLESPACE USERS_INDEX PCTFREE 10
STORAGE(INITIAL 51200 NEXT 51200 PCTINCREASE 1 ) ;
CREATE INDEX IT_NDX_S_TAX_TYPE_FK9 ON
IT_TBL_T_TAX_TXN_DETAILS(TAX_TYPE)
TABLESPACE USERS_INDEX PCTFREE 10
STORAGE(INITIAL 51200 NEXT 51200 PCTINCREASE 1 ) ;
------------------------------ IMPORTANT NOTICE - AVIS IMPORTANT
------------------------------
Computer viruses can be transmitted via email. Recipient should check
this email and any attachments for the presence of viruses. Sender and
sender company accept no liability for any damage caused by any virus
transmitted by this email.
This email transmission and any accompanying attachments contain
confidential information intended only for the use of the individual or
entity named above. Any dissemination, distribution, copying or action
taken in reliance on the contents of this email by anyone other than the
intended recipient is strictly prohibited. If you have received this
email in error please immediately delete it and notify sender at the
above email address.
Le courrier electronique peut etre porteur de virus informatiques. Le
destinataire doit donc passer le present courriel et les pieces qui y
sont jointes au detecteur de virus. L' expediteur et son employeur
declinent toute responsabilite pour les dommages causes par un virus
contenu dans le courriel.
Le present message et les pieces qui y sont jointes contiennent des
renseignements confidentiels destines uniquement a la personne ou a l'
organisme nomme ci-dessus. Toute diffusion, distribution, reproduction
ou utilisation comme reference du contenu du message par une autre
personne que le destinataire est formellement interdite. Si vous avez
recu ce courriel par erreur, veuillez le detruire immediatement et en
informer l' expediteur a l' adresse ci-dessus.
------------------------------ IMPORTANT NOTICE - AVIS IMPORTANT
------------------------------
__________
View the list's information and change your settings at
http://www.freelists.org/list/programmingblind
__________
View the list's information and change your settings at
http://www.freelists.org/list/programmingblind
- References:
- Re: PL.SQL question- Can anyone explain why the Oracle SQL engine is
- From: Peter Quaiattini
Other related posts:
- » Re: PL.SQL question- Can anyone explain why the Oracle SQL engine is
- » RE: PL.SQL question- Can anyone explain why the Oracle SQL engine is
- Re: PL.SQL question- Can anyone explain why the Oracle SQL engine is
- From: Peter Quaiattini