Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Create Function Based Index.

Posted on 2009-12-22
20
Medium Priority
?
837 Views
Last Modified: 2013-12-18
Hi Experts,

I am facing very strange behavior while creating an Function based index.

Here is script.
CREATE INDEX ED_ELIGIBILITY_F1 ON ED_ELIGIBILITY
(NVL(PAYMENT_END_DT,TO_DATE('2999-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
TYPE_OF_ASSISTANCE_CD, CG_STATUS_CD, CURRENT_ELIG_IND, PROGRAM_CD)
tablespace &ts_i_name
storage (freelists &flists)
initrans 128
pctfree &pfree
parallel 3;

Here is the script to validate changes.
select dbms_metadata.get_ddl('INDEX', 'ED_ELIGIBILITY_F1') data from dual;

Output:
  CREATE INDEX "TIERS2OWN"."ED_ELIGIBILITY_F1" ON "TIERS2OWN"."ED_ELIGIBILITY" (NVL("PAYMENT_END_DT",TO_DATE(' 2999-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')), "TYPE_OF_ASSISTANCE_CD", "CG_STATUS_CD", "CURRENT_ELIG_IND", "PROGRAM_CD")
  PCTFREE 10 INITRANS 128 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "ELIG_INDX"
  PARALLEL 3

We got differences :
We provided date and time without any space, but got space before 2999.

' 2999-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'

Thanks
0
Comment
Question by:Devinder Singh Virdi
  • 11
  • 6
  • 2
  • +1
20 Comments
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26108754
Actually we are going to remove 50% of data from many tables, and instead of delete we are doing Create Table As Select, then we are creating the indexes.
We are going to do this activity on 24th. therefore I have only 2 days. Even if we use production script, we are getting space before 2999 and additional character 's' in 'syyyy-mm'.
We tested this script in two full size environment and both of them has space, while production doesn't have.
So please reply ASAP
0
 
LVL 18

Expert Comment

by:sventhan
ID: 26109403
Virdi -
Do you have problem creating scripts using DBMS_METADATA? Is that what do you like to fix?
Did you try it in TOAD? Are you seeing the same behaviour?
Why 125 points? If you increase you'll get more attention.
 
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26109438
use trim function and forget the space ;)

CREATE INDEX ED_ELIGIBILITY_F1 ON ED_ELIGIBILITY
(NVL(PAYMENT_END_DT,TO_DATE(trim('2999-12-01 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')),
TYPE_OF_ASSISTANCE_CD, CG_STATUS_CD, CURRENT_ELIG_IND, PROGRAM_CD)
tablespace &ts_i_name
storage (freelists &flists)
initrans 128
pctfree &pfree
parallel 3;
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26110599
i think the dbms_metadata retrieves in syyyy-mm-dd format.
please give output for

SELECT * FROM V$NLS_Parameters ;
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26113037
Shajukg,
I attached the file. There are no differences in Production and this environment parameters. Let me check for all parameters.

sventhan: I increased the point. Thats the good idea to do it from TOAD. May be OS parameters is overriding Oracle parameter.

Thanks
Function-Based-Index.txt
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26113394
sventhan: I tried creating index using TOAD, again I am getting space.

Let me create a very small table out of ED_ELIGIBILITY in production and check.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 26113441
Yes. I did check that in Toad.
The other option is you could create the metadata out of export dump.
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26113476
I tested in Production(within different user as well) and found space. But original index doesn't have space in it.
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26113538
>> The other option is you could create the metadata out of export dump.
Sorry, I didn't get your point.

HainKurt:
>> use trim function and forget the space ;)
We cannot change defination of index because this index is present for long time and online application is using this. We have lots of query tuned(online plus batch), which uses this index.
I am afraid that if I change the index defination, many application might stop working properly.

0
 
LVL 18

Expert Comment

by:sventhan
ID: 26113584
what I mean is
using oracle datapump with this option
CONTENT=METADATA_ONLY  
you could be able to create the metadata for the specific dbobjects.
 
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26113831
I dont think DBMS_METADATA is creating the problem. I cannot see space as well as 'S' for original index using same medata_data command. We are creating a copy of table for those records which we want to retain and then we have to create the indexes. So we need to find, why space is got added, though we are not providing any space.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 26113885
I created the index using the sql you posted and it creates the space on dates.
Then it could be wrong with the ddl. Do you see any difference when you compare the orginal with the ddl posted above?
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26114321
I took production script, set nls_date_format to 'yyyy-mm....' and create the index, it is again showing the with 'syyyy-mm....'

0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26114383
Can you please try to find if this is a bug/change in 10.2.0.4?
This index was created when database was in 9i.
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26115368
WOW!
I started "monitoring usage" featue of Index, and happy to see that oracle is using this indexe.
In Last Test environment, while doing load test, it is showing that index is used.

select index_name, monitoring, used from v$object_usage where index_name ='ED_ELIGIBILITY_F1';

INDEX_NAME                     MON USE
------------------------------ --- ---
ED_ELIGIBILITY_F1              YES YES


Thanks all for your help.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26115370
what tool are you using to create the index?

put the script in a text file and run from sqlplus

I remember Toad is creating such space in default values... maybe the tool is creating that space like this

'yyyy-mm-dd'
->
'
yyyy-mm-dd'
->
'syyyy-mm-dd'
0
 
LVL 18

Accepted Solution

by:
sventhan earned 2000 total points
ID: 26115377
I already did a search in metalink but could not find any. Do you have 2 different versions in TEST and PROD.

mine is 10.2.0.4 too?


I'll try changing the date format and see if it works.
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26115455
HainKurt:
No all environments are in same oracle version. I tried using sqlplus as well.

sventhan: Thanks for your efforts, we are also woriking on same version.
Since we are using reducing database size to 1TB appx, I was worried if application stop using this index after rebuilding new tables and indexes. But we did some tests and oracle is using this index, so we are good to go with this change.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 26115574
Thanks for the points!!!

Happy Holidays !
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
ID: 26115650
I just got the update from Oracle that this is because of Oracle BUG.
For more information please see
SR 3-1247564841: Space in Function Based Index for Date column

Bug.7126874 (92) FUNCTION BASED INDEX CREATED WRONGLY IN 10.2.0.4 V1020:

<pre><i>This is a side effect of Bug 4490782</i>
<i>"syyyy" means signed yyyy, </i>

If I get anything about this from Oralce I will let you know.
Thanks for Help
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: You must have administrative privileges in order to create/edit Roles. Salesforce.com (http://www.salesforce.com/) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and marke…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question