Home » SQL & PL/SQL » SQL & PL/SQL » Index creation!! issue in production (merged)
Index creation!! issue in production (merged) [message #312572] Wed, 09 April 2008 05:36 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I have 3 sets of queries

first one

[SIZE=3]SELECT /*+FIRST_ROWS*/ abacc_best_address_line_1,
abacc_best_address_line_2,
abacc_best_address_line_3,
abacc_best_address_line_4,
abacc_best_postcode,
abacc_std_business_name,
abacc_main_tel_no,
NVL(RTRIM(abacc_tps_consent),'C')
INTO lv_best_add_line1,
lv_best_add_line2,
lv_best_add_line3,
lv_best_add_line4,
lv_best_postcode,
lv_business_name,
lv_telephone_number,
lv_busname_tps
FROM ACE2_BUSNAME_AND_CONSENT_CMPNY
WHERE abacc_legal_entity_code = CUGTable(i).sme_id
AND ((NVL(abacc_std_business_name,' ') <> ' ') OR
(LENGTH(NVL(abacc_main_tel_no,' ')) > Cool)
AND NVL(abacc_cni_ind, ' ') = ' '


In other queries aLL THE THINGS ARE SAME BUT EXCEPT THE WHERE CONDITION

2nd query where condn-

abacc_legal_entity_code = CUGTable(i).sme_id
AND NVL(RTRIM(abacc_dnm_consent), 'C') = 'C'
AND LENGTH(NVL(abacc_best_postcode,' ')) > 4
AND NVL(abacc_cni_ind, ' ') = ' '

3nd query where condn-

abacc_legal_entity_code = CUGTable(i).sme_id
AND NVL(RTRIM(abacc_dnm_consent), 'C') = 'C'
AND ((NVL(abacc_std_business_name,' ') <> ' ') OR
(LENGTH(NVL(abacc_main_tel_no,' ')) > Cool)
AND LENGTH(NVL(abacc_best_postcode,' ')) > 4
AND NVL(abacc_cni_ind, ' ') = ' '

Now i have created a function baased index

create index fun_ind1 ON ACE2_BUSNAME_AND_CONSENT_CMPNY
(abacc_legal_entity_code,nvl(abacc_cni_ind,''))

but it doesn't solve the purpose greatly as the cpu usage % is reduced slightly

here i am doing a test of around 60 records in production i have 20 million

and if i try to create one more functional index that is not used.how to implove more performance.

please help its urgent

Re: URGENT iNDEX CREATION IN PRODUCTION [message #312573 is a reply to message #312572] Wed, 09 April 2008 05:44 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
please help its urgent

Please search these forums for the word urgent, see what sort of reaction, use of that word tends to get (and deservedly so) Also have a look at the guidelines and what they say about posting in uppercase as well as how to format your posts.
Cheers
Jim
Re: URGENT iNDEX CREATION IN PRODUCTION [message #312576 is a reply to message #312573] Wed, 09 April 2008 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Carefully note that the title is not in full upper case. ./fa/1704/0/

Regards
Michel
Re: URGENT iNDEX CREATION IN PRODUCTION [message #312578 is a reply to message #312576] Wed, 09 April 2008 05:52 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
LOL. nope, neither it is. Good spot. I would guess then that the OP accidentally had the caps lock on (and intended all to be in lower case but with a capitalised 'Index').
Index creation!! issue in production [message #312580 is a reply to message #312572] Wed, 09 April 2008 06:03 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi reposting the query,Kindly help

I have 3 sets of queries

first one

[SIZE=3]SELECT /*+FIRST_ROWS*/ abacc_best_address_line_1,
abacc_best_address_line_2,
abacc_best_address_line_3,
abacc_best_address_line_4,
abacc_best_postcode,
abacc_std_business_name,
abacc_main_tel_no,
NVL(RTRIM(abacc_tps_consent),'C')
INTO lv_best_add_line1,
lv_best_add_line2,
lv_best_add_line3,
lv_best_add_line4,
lv_best_postcode,
lv_business_name,
lv_telephone_number,
lv_busname_tps
FROM ACE2_BUSNAME_AND_CONSENT_CMPNY
WHERE abacc_legal_entity_code = CUGTable(i).sme_id
AND ((NVL(abacc_std_business_name,' ') <> ' ') OR
(LENGTH(NVL(abacc_main_tel_no,' ')) > )
AND NVL(abacc_cni_ind, ' ') = ' '


In other queries aLL THE THINGS ARE SAME BUT EXCEPT THE WHERE CONDITION

2nd query where condn-

abacc_legal_entity_code = CUGTable(i).sme_id
AND NVL(RTRIM(abacc_dnm_consent), 'C') = 'C'
AND LENGTH(NVL(abacc_best_postcode,' ')) > 4
AND NVL(abacc_cni_ind, ' ') = ' '

3nd query where condn-

abacc_legal_entity_code = CUGTable(i).sme_id
AND NVL(RTRIM(abacc_dnm_consent), 'C') = 'C'
AND ((NVL(abacc_std_business_name,' ') <> ' ') OR
(LENGTH(NVL(abacc_main_tel_no,' ')) > )
AND LENGTH(NVL(abacc_best_postcode,' ')) > 4
AND NVL(abacc_cni_ind, ' ') = ' '

Now i have created a function baased index

create index fun_ind1 ON ACE2_BUSNAME_AND_CONSENT_CMPNY
(abacc_legal_entity_code,nvl(abacc_cni_ind,''))

but it doesn't solve the purpose greatly as the cpu usage % is reduced slightly

here i am doing a test of around 60 records in production i have 20 million

and if i try to create one more functional index that is not used.how to implove more performance.

please help its urgent
Re: Index creation!! issue in production [message #312582 is a reply to message #312580] Wed, 09 April 2008 06:09 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Hi,
Whenever you post code, always post it between code tags:

[code] Your code goes here [/code] It makes it much easier to read.
To me
Quote:
ere i am doing a test of around 60 records in production i have 20 million
Is your biggest issue. Testing on 60 records is going to tell you exactly nothing about how the query will work on 20 million records (unless you are using statistics gathered from the production db).
In Oracle an empty string ('') is basically the equivalent to a NULL therefore NVL(column,'') is the same as saying:
if column is Null then make it .... Null.
To test to see if a column is null, use
WHERE column_name IS NULL

And once again STOP USING THE WORD URGENT (and yes, I meant to shout there)

[Updated on: Wed, 09 April 2008 06:11]

Report message to a moderator

Previous Topic: Trigger Problem
Next Topic: repalce the first occurance of a character
Goto Forum:
  


Current Time: Sun Dec 11 00:08:45 CST 2016

Total time taken to generate the page: 0.20480 seconds