Why skipped?
A reader, January 09, 2006 - 4:51 pm UTC
Why does the procedure skip creating the index if you just created the table with no indexes? I was expecting to see "created".
I see it
A reader, January 09, 2006 - 4:52 pm UTC
Sorry for the dumb question.
Didn't see you executed the procedure twice! Duh!
Original idea okay
James Valenti, January 09, 2006 - 5:07 pm UTC
Your original idea is okay except you need to use execute immediate for ddl statements inside a PL/SQL block. Execute immediate 'Create index...'.
A reader, January 09, 2006 - 5:25 pm UTC
INDEX
P.Kartihck, January 10, 2006 - 6:41 am UTC
DECLARE
A NUMBER(1) := 0;
BEGIN
SELECT CASE WHEN EXISTS(SELECT * FROM USER_INDEXES WHERE INDEX_NAME = 'PK_AREA_CODE')
THEN 1
ELSE 0
END CASE INTO A
FROM DUAL;
IF A = 1
THEN
DBMS_OUTPUT.PUT_LINE('INDEX EXIST');
ELSE
DBMS_OUTPUT.PUT_LINE('NEW INDEX');
END IF;
END;
This works fine..
Tom in your reply you have used exception_init and associated the error number -955 with the user defined exception right. So how does oracle raised this exception i.e. -955 does it internally runs a select against a data dictionary table to find the index exist or not. Do we end up with any problem if we do the check manually as i have done above.
January 10, 2006 - 8:30 am UTC
plsql runtime engine says "when error occurs, look to see if programmer mapped this error code - the sqlcode - to an exception, if so, raise that named exception"
I find the exception based code to be "more clear" as to what is happening, and it saves us the time and resources of running a query to see if we should be doing something.
It goes along with me "not liking to see select count(*) queries in programs". 9999 times out of 10000 - the code looks like:
select count(*) into l_cnt from t where condition;
if ( l_cnt > 0 ) -- we found data to process!!!
then
process_data;
end if;
Me, I would just code:
process_data; -- if process_data isn't smart enough to know to return
-- when there is no data to process, make it smarter
Less work over all.
Same concept here. Why bother to look if the index exists yourself when the create index statement will do it for you (it'll do it anyway - you cannot stop it from looking - so why do it TWICE)
sql server vs oracle
Connor, January 10, 2006 - 9:02 am UTC
just wanted to add a comment that adding an index on the fly *might* be genuine need in Oracle, but just because you've done it that way with sql server doesn't necessarily mean you should be creating indexes on the fly with oracle...
hth
Connor
Thank you!
Peter Olson, January 10, 2006 - 9:09 am UTC
> Remember, that which we know is "easy", that which we don't know seems
> impossible - until you "know", then it is easy.
True enough, though I would add that experience can often tell us when the "impossible" is possible; when something that is difficult ought to be easy.
Thank you for the very helpful response! I like the use of the exception as opposed to checking the user_indexes table. It is a bit more elegant, and it allows for the fact that Oracle checks more than just the name of the index to know if it already exists.
Two follow-up questions:
1. You said, "This is one way:", which would seem to imply that there are other ways. What are some of these other ways? I would like to know so that I can learn about them and understand the pros and cons of each.
2. In my particular case, I need to conditionally create *multiple* indexes...each independent of the other. For example, if indexes 1 and 3 are present, but not 2, I want to make sure to add 2 without failing on 1 or 3. It seemed appropriate to share the declaration of the exception as follows:
declare
already_exists exception;
pragma exception_init( already_exists, -955 );
begin
begin
execute immediate 'create index t_idx_1 on t(x)';
dbms_output.put_line( 'Created index 1' );
exception
when already_exists then
dbms_output.put_line( 'Index 1 already present' );
null;
end;
begin
execute immediate 'create index t_idx_2 on t(y)';
dbms_output.put_line( 'Created index 2' );
exception
when already_exists then
dbms_output.put_line( 'Index 2 already present' );
null;
end;
begin
execute immediate 'create index t_idx_3 on t(z)';
dbms_output.put_line( 'Created index 3' );
exception
when already_exists then
dbms_output.put_line( 'Index 3 already present' );
null;
end;
end;
This is a bit more verbose than I might like, but it seems acceptable to me. Are there any pitfalls or problems with this approach? Better ways?
January 10, 2006 - 9:28 am UTC
1) other ways would be sqlplus "tricks" for example - here is one:
-------------------------------------------
drop table t;
create table t ( x int );
set heading off
set feedback off
spool tmp.sql
select 'create index t_idx on t(x);'
from dual
where not exists ( select null
from user_indexes
where index_name = 'T_IDX' );
spool off
set feedback on
set heading on
@tmp.sql
-------------------------------------------------
there are likely an infinite number of ways to do this.
2) use a local procedure to modularize repetitive code:
ops$tkyte@ORA9IR2> create table t ( x int, y int, z int );
Table created.
ops$tkyte@ORA9IR2> declare
2 already_exists exception;
3 pragma exception_init( already_exists, -955 );
4 procedure do_it( p_str in varchar2 )
5 is
6 begin
7 execute immediate p_str;
8 dbms_output.put_line( 'Ran: ' || substr( p_str, 1, 40 ) );
9 exception
10 when already_exists then
11 dbms_output.put_line( 'Already Exists ' || substr( p_str, 1, 40 ) );
12 end;
13 begin
14 do_it( 'create index t_idx_1 on t(x)' );
15 do_it( 'create index t_idx_2 on t(y)' );
16 do_it( 'create index t_idx_3 on t(z)' );
17 end;
18 /
Ran: create index t_idx_1 on t(x)
Ran: create index t_idx_2 on t(y)
Ran: create index t_idx_3 on t(z)
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> declare
2 already_exists exception;
3 pragma exception_init( already_exists, -955 );
4 procedure do_it( p_str in varchar2 )
5 is
6 begin
7 execute immediate p_str;
8 dbms_output.put_line( 'Ran: ' || substr( p_str, 1, 40 ) );
9 exception
10 when already_exists then
11 dbms_output.put_line( 'Already Exists ' || substr( p_str, 1, 40 ) );
12 end;
13 begin
14 do_it( 'create index t_idx_1 on t(x)' );
15 do_it( 'create index t_idx_2 on t(y)' );
16 do_it( 'create index t_idx_3 on t(z)' );
17 end;
18 /
Already Exists create index t_idx_1 on t(x)
Already Exists create index t_idx_2 on t(y)
Already Exists create index t_idx_3 on t(z)
PL/SQL procedure successfully completed.
RE: sql server vs oracle
A reader, January 10, 2006 - 9:21 am UTC
> just wanted to add a comment that adding an index on the fly *might* be genuine
> need in Oracle, but just because you've done it that way with sql server doesn't
> necessarily mean you should be creating indexes on the fly with oracle...
A clarification to the original post: I'm working on upgrade script that will run *during installation*, so I am not creating an index "on the fly" (at least not in my mind). As far as I can recall, I've never created an index on the fly in SQL Server.
I expect that it would be rare to find circumstances that require creating an index on the fly...whether using Oracle or SQL Server.
Peter
Even more flexible.
Andrew, January 10, 2006 - 9:27 am UTC
Although I generally do not like to use exception handling for application flow control -- maby because exception handling is a hard branch and my old COBOL and PL/I habits are hard to break -- I do think this is a most flexible approach for updating the schema objects. The main reason is because it is easy to add additional conditions -- like -1408 (index on those columns already exists). It does not, for example, prevent the tool from creating redundant indexes in those cases where the customer has already created a servicable index that includes additional columns (even with the approval of tech support).
Index
P.karthick, January 10, 2006 - 9:29 am UTC
thank you tom.
so will create index will internally be executing a select to find the existance of that particular index or it does it in a different way.
January 10, 2006 - 9:33 am UTC
No, they'll just try to create it, get a duplicate, raise an error.
It is rather pointless to select to see if something exists and then create it in a multi-user environment. So what - it doesn't exist right now, that does not mean it won't exist in a fraction of a second from now. (this is why I hate code that does a select count(*) to see if there is anything to "process" - just process anything you find - rather POINTLESS to count and then process, just process...)
Updating indexes
Bob B, January 10, 2006 - 9:30 am UTC
If this is for an upgrade script, it might be better to drop the index (catching an exception if the drop fails) and then creating the index. Just because the index is on the right columns doesn't mean its the *same* index. The new index could use key compression, change storage characteristics, or be partitioned, to name a few of the possibilities
Select .. Into
P.Karthick, January 10, 2006 - 9:36 am UTC
when talking about select count(*) thing.. the main problem i come acrosse is using select x into y...
when there is no data in the table i end up with no_data_found exception. is it possible to avoide it
i.e. let me say
i initialize a varaible x := 0
then store a value useing select .. into x
if the select return a value store it else go with the zero
why iam asking this is because most of the time in the code i see select count(*)... before doing a select .. into
how will you do it tom..
January 10, 2006 - 10:00 am UTC
you would just code:
..... your other code here.....
begin
select x into y from t where condition;
exception
when no_data_found then y := <default>;
end;
...... rest of your code here......
never do the count(*) - the count(*) assures you of NOTHING. Well, nothing beyond the fact that when you did the count(*) there were count(*) number of rows. It doesn't mean that when you go to do the select into - any data will be there.
Use the language..
Exception
P.Karthick, January 10, 2006 - 10:30 am UTC
DECLARE
NAME VARCHAR2(100) := 'EMPTY';
BEGIN
SELECT ENAME INTO NAME FROM SCOTT.EMP WHERE ENAME = 'NOTHING';
DBMS_OUTPUT.PUT_LINE(NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN NAME := 'EXCEPTION';
END;
now i want my dbms_output to print 'EXCEPTION'. how do i do that can even use GOTO
January 10, 2006 - 11:01 am UTC
DECLARE
NAME VARCHAR2(100) := 'EMPTY';
BEGIN
begin
SELECT ENAME INTO NAME FROM SCOTT.EMP WHERE ENAME = 'NOTHING';
EXCEPTION
WHEN NO_DATA_FOUND THEN NAME := 'EXCEPTION';
end;
DBMS_OUTPUT.PUT_LINE(NAME);
END;
precisely like I just showed right above??!?
A reader, January 10, 2006 - 11:59 am UTC
I like Connor's comment and the locations, always on the move :-)