|
 |
| Recent blog posts: | |
|
| They have birthday today: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Saturday, 12 August 2006
|
| Oracle database not starting up after reboot on RedHat ES4 Linux ! Shahid Bhatti 03:59:06 |
| | Hi,
I have Oracle database enterprise edition 9.2.0.4 running on RedHat
Linux ES release 4. Although the installation went fine and after the
installation I was able to see the common processes like ora_pmon
started in system, but when I reboot system the database does not
comeup.
I am using dbstart command via a /etc/init.d/oracle file. This is
linked to /etc/rc5.d and /etc/rc3.d as well in the Linux server.
The amazing thing is that tnslistener is started automatically after
system reboot but not database. That is, when I check the status of
that via the command "lsnrctl status" it shows me the one database
instance that I am hoping to see correctly. But no ora_pmon etc
processes are there.
Even interesting thing is that when I manually run the command
"/etc/init.d/oracle start" by hand, it does start the database
perfectly fine. This is the same command that I am running via the
startup scripts in /etc/rc5.d and othe runlevels.
Please help me finding out why is the database not starting up
automatically? I have checked the
/u02/app/oracle/product/9.2.0/startup.log which just says reports that
Listener has started but after that it does not show anything about
database starting up.
Below I am pasting the chunk of /etc/init.d/oracle script that is
supposed to start the listener and database. Please have a look at it
and tell me what can I be doing wrong in this case?
#################################################
ORACLE=oracle1
export ORACLE_HOME PATH
#
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
#
case $1 in
'start')
echo "$0: starting up" >> $LOG
date >> $LOG
# Start Oracle Net
if [ -f $ORACLE_HOME/bin/tnslsnr ] ; then
echo "starting Oracle Net listener"
su - oracle1 -c "$ORACLE_HOME/bin/lsnrctl start" >> $LOG 2>&1 &
fi
echo "starting Oracle databases"
su - oracle1 -c "$ORACLE_HOME/bin/dbstart" >> $LOG 2>&1
;;
########################################################
Thanks in advance for your urgent help.
--Smb
|
| |  5 answers | Add comment |
|
| recovery aproach ... Guest 03:48:49 |
| | Hello,
Sorry about starting new thread but this question is about another
aproach.
- we have the latest datafiles about 1 hour long
- only problematic tablespace/datafile is, let`s say system01.dbf
Question is:
Is there any possibility to create new database and then replacing good
datafiles and then do some "magic"?
Is there any kind of "magic" step?
Sorry, I know it looks crazy but trying everythink.
Thanks for answers
Robert
|
| | 6 answers | Add comment |
|
| data in Oracle Guest 02:52:21 |
| | Does all data in Oracle need to be in a table?
If you just want a simple lone integer that counts, say, the number of
users in your database, is there an easy way to create that?
Thanks.
|
| | 7 answers | Add comment |
|
| how to decide SHMMAX,SHMMNI and SHMMIN Peter 02:43:37 |
| | Please tell me, how to decide SHMMAX,SHMNI and SHMMIN in Linux Kernal
version 4 for Oracle 9i installation when my database will be very
huge, somewhere 1/2 Terabytes.
|
| | 4 answer | Add comment |
|
| check if a command has failed Guest 01:34:24 |
| | How does one typically check if a command has failed in PL/SQL?
Thank you.
|
| | 2 answer | Add comment |
|
| Syntax error - what is wrong Guest 01:31:55 |
| | What is wrong with this?
create or replace PACKAGE DAL_TABLE_METHODS AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GET_EMP (
p_EMPNO IN NUMBER,
cur_EMP OUT T_CURSOR
);
PROCEDURE UPDATE_EMP (
pEMPNO IN NUMBER,
pENAME IN VARCHAR2,
pJOB IN VARCHAR2,
pMGR IN NUMBER,
pHIREDATE IN DATE,
pSAL IN NUMBER,
pCOMM IN NUMBER,
pDEPTNO IN NUMBER
);
PROCEDURE DELETE_EMP (
p_EMPNO IN NUMBER
);
PROCEDURE INSERT_EMP (
pEMPNO OUT NUMBER,
pENAME IN VARCHAR2,
pJOB IN VARCHAR2,
pMGR IN NUMBER,
pHIREDATE IN DATE,
pSAL IN NUMBER,
pCOMM IN NUMBER,
pDEPTNO IN NUMBER
);
END DAL_TABLE_METHODS;
CREATE OR REPLACE
PACKAGE BODY DAL_TABLE_METHODS AS
PROCEDURE GET_EMP (
p_EMPNO IN NUMBER,
cur_EMP OUT T_CURSOR
)
IS
BEGIN
OPEN cur_EMP FOR
SELECT
"EMPNO"
, "ENAME"
, "JOB"
, "MGR"
, "HIREDATE"
, "SAL"
, "COMM"
, "DEPTNO"
FROM EMP
WHERE (EMPNO = p_EMPNO);
END GET_EMP;
PROCEDURE UPDATE_EMP (
pEMPNO IN NUMBER,
pENAME IN VARCHAR2,
pJOB IN VARCHAR2,
pMGR IN NUMBER,
pHIREDATE IN DATE,
pSAL IN NUMBER,
pCOMM IN NUMBER,
pDEPTNO IN NUMBER
)
IS
BEGIN
UPDATE EMP
SET "ENAME" = pENAME
, "JOB" = pJOB
, "MGR" = pMGR
, "HIREDATE" = pHIREDATE
, "SAL" = pSAL
, "COMM" = pCOMM
, "DEPTNO" = pDEPTNO
WHERE (EMPNO = p_EMPNO);
END UPDATE_EMP;
PROCEDURE DELETE_EMP (
p_EMPNO IN NUMBER
)
IS
BEGIN
DELETE FROM EMP
WHERE (EMPNO = p_EMPNO);
END DELETE_EMP;
PROCEDURE INSERT_EMP (
pEMPNO OUT NUMBER,
pENAME IN VARCHAR2,
pJOB IN VARCHAR2,
pMGR IN NUMBER,
pHIREDATE IN DATE,
pSAL IN NUMBER,
pCOMM IN NUMBER,
pDEPTNO IN NUMBER
)
IS
BEGIN
pEMPNO := DEVELOPER.EMPNO_SEQUENCE.NEXTVAL;
INSERT INTO "EMP" (
"EMPNO"
,"ENAME"
,"JOB"
,"MGR"
,"HIREDATE"
,"SAL"
,"COMM"
,"DEPTNO"
)
VALUES (
pEMPNO
,pENAME
,pJOB
,pMGR
,pHIREDATE
,pSAL
,pCOMM
,pDEPTNO
);
END INSERT_EMP;
END DAL_TABLE_METHODS;
|
| | 2 answer | Add comment |
|
| Stopping Oracle Jobs Amerar@Iwc.Net 01:09:17 |
| |
Hi All,
We are running Oracle 9.2.0.7. We have a number of jobs running in the
queue. A few things have become an issue here.
First, if we need to re-compile a stored procedure that is being used
by one of the jobs, the job must first be stopped. Strangely enough,
even if we want to issue a simple grant to a table or on a procedure,
we cannot as long as the object is being used......?? Why???
However, even if we use DBMS_JOB.REMOVE to remove the job from the
queue, for some reason the JQ locks are never released, and thus I
cannot comple or grant. I need to manually go and kill the
session........
Any thoughts or ideas?
Thanks!
|
| | 1 answer | Add comment |
|
| exclusively locked table Guest 00:53:29 |
| | If a table is exclusively locked and someone else tries an insert on
it, will Oracle simply wait forever for it to be unlocked before doing
the insert?
Thanks.
|
| | 2 answer | Add comment |
|
| HOW TO AVOID NOT EXISTS IN THIS QUERY TO INCREASE PERFORMANCE Venkat Oar 00:29:40 |
| | Hi,
the query below really kills, i am sure there is a way to finetune this
query but not sure how to do it. Appreciate any help.
SELECT DISTINCT A.prod_sn
, TO_CHAR(a.est_bld_dt, 'YYYY-MM-DD')AS "Build_Date"
, SUBSTR(a.LN_SEQ_NO,8,3) AS "seq_no"
, c.sales_model_id
, c.fac_prod_fam_cd
FROM v_mes_supp_ord A
, mes_rte_trak b
, QWB2_SN_PFX c
WHERE A.prod_sn IS NOT NULL
AND a.LN_SEQ_NO IS NOT NULL
AND TRUNC(a.est_bld_dt) <= TRUNC(SYSDATE + 10)
AND a.sn_pfx = c.sn_pfx
AND c.fac_prod_fam_cd != 'MISC'
AND NOT EXISTS (SELECT B2.Mov_No FROM MES_SUPP_ORD_ASSEM
A2,MES_RTE_TRAK B2
WHERE A2.supp_ord_tag = A.supp_ord_egg_tag AND
A2.supp_ord_assem_tag = B2.supp_ord_assem_tag
AND b2.mov_no = 1 AND b2.area_id IN ('16C011280','16C011910'))
ORDER BY "Build_Date", "seq_no"
Thanks in advance
--Venkat
|
| | 4 answer | Add comment |
Friday, 11 August 2006
|
| Generating Hierarchical XML using CONNECT BY with Oracle 9i Guest 23:17:46 |
| | I need to create structured XML based on a simple parent/child
relationship. Take a table with three columns as an example:
Objects Table:
Unique_ID (primary key)
Parent_ID (foreign key to objects table unique_id)
Name
Data might look something like this:
1, NULL, 'Parent'
2, 1, 'Child 1'
3, 1, 'Child 2'
4, 2, 'Grandchild 1'
5, 2, 'Grandchild 2'
6, 3, 'Grandchild 3'
7, 3, 'Grandchild 4'
I want to turn this data into XML that looks like this:
<OBJECT id="1">
<OBJECT_NAME>Parent</OBJECT_NAME>
<OBJECT id="2">
<OBJECT_NAME>Child 1</OBJECT_NAME>
<OBJECT id="4">
<OBJECT_NAME>Grandchild 1</OBJECT_NAME>
</OBJECT>
<OBJECT id="5">
<OBJECT_NAME>Grandchild 2</OBJECT_NAME>
</OBJECT>
</OBJECT>
<OBJECT id="3">
<OBJECT_NAME>Child 1</OBJECT_NAME>
<OBJECT id="6">
<OBJECT_NAME>Grandchild 3</OBJECT_NAME>
</OBJECT>
<OBJECT id="7">
<OBJECT_NAME>Grandchild 4</OBJECT_NAME>
</OBJECT>
</OBJECT>
</OBJECT>
...using a single query. I know I can do this in Oracle 10, using
dbms_xmlgen.newcontextFromHierarchy and CONNECT BY. Does anyone have
any idea how do do this in Oracle 9i?? (specifically 9.2.0.7)
My only though so far is to manually constuct the XML in a CLOB using a
PLSQL function, but I'm loathed to do something so ugly.
Many thanks in advance for any contributions..
|
| | 2 answer | Add comment |
|
| Appending XMLELEMENTs to existing XMLType Guest 22:04:39 |
| | I have a self referencing table that I'm trying to build an XML
hierarchy with. The table Categories layout looks like this:
CategoryID Number NOT NULL Primary,
ParentID Number,
Name varchar2(200),
isActive Number(0,1)
-----------------
I am using this query so far to pull out the XML elements:
----------------------
SELECT xmlelement( "Categories",
xmlagg( xmlelement( "Category",
xmlattributes( cata.categoryid as "id",
cata.name as "name"),
xmlelement( "Category",
xmlattributes( catb.categoryid as
"id", catb.name as "name"))
)
)
).getclobval()
FROM Maps.Categories cata, Maps.Categories catb
WHERE catb.ParentID = cata.CategoryID
------------------------
However, what I want is for the self-referencing hierarchy to be
displayed as an xmlagg of nested nodes. Is there a way I can do that
with a simple query, or will I have to write a recursive function to
dig down each level? If I must take this approach, does anyone know how
to append the XML (in varchar2 or XMLType) nodes to the parent node?
For example (psuedocode):
function processCategory(catID IN Number) returns XMLNode {
(string or xmltype) catXML = getCategoryXML();
for each childNode in (SELECT * FROM Categories WHERE parentID =
catID)
catXML = concatenate processCategory(childID)
end for loop
return catXML
}
---------------
Right now I get xml that looks like this:
<Categories><Category id="1" name="Categories"><Category id="2"
name="ParentCat1" /></Category><Category id="1"
name="Categories"><Category id="3" name="ParentCat2"
/></Category></Categories>
What I want is:
<Categories>
<Category id="2" name="ParentCat1">
<Category id="6" name="ParentCat1Sub1" />
</Category>
<Category id="3" name="ParentCat2" />
...
</Categories>
Any help would be appreciated as I'm on a tight timeline and if I can't
get this XML stuff working I will have to switch to a relational model
instead and process the code on the frontend (.NET).
Thanks in Advance,
-dpx
|
| | 1 answer | Add comment |
|
| Composite index and data distribution Micha Kuratczyk 20:13:21 |
| | Hi,
I've observed a behaviour that I cannot explain by myself
and I would be glad for help. Here it goes (10gXE
but tested on 9.2.0.5 EE too):
SIMPLE TABLE WITH TWO COLUMNS:
create table foobar(foo number not null, bar number not null);
Table created.
DATA DISTRIBUTION: IN 1% OF THE ROWS FOO=1, IN 99% FOO=99:
begin 2 for i in 1..1000000 loop
3 insert into foobar(foo, bar) values (case when i <= 10000 then 1 else
99 end, i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
COMPOSITE INDEX (BEST I CAN THINK OF FOR THE QUERY TO BE EXECUTED):
create index foobar_idx on foobar(foo, bar);
Index created.
DETAILED STATISTICS (SO IT'S NOT STATISTICS-RELATED ISSUE):
exec dbms_stats.gather_table_stats(user, 'FOOBAR', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto');
PL/SQL procedure successfully completed.
JUST TO BE SURE - DATA DISTRIBUTION:
select foo, count(*) from foobar group by foo;
FOO COUNT(*)
---------- ----------
1 10000
99 990000
TOOLS:
set timing onSQL> set autotrace traceonlySQL> alter session set sql_trace=true;
Session altered.
Elapsed: 00:00:00.08
AND HERE COMES THE QUERY FOR FOO=1 (1% OF THE ROWS):
select /*+ FIRST_ROWS(25) */ * from (select foo, rownum rn 2 from foobar where foo=1 order by bar) where rn <= 25;
25 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2407035716
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 650 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 25 | 650 | 3 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
|* 3 | INDEX RANGE SCAN| FOOBAR_IDX | 25 | 175 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=25)
3 - access("FOO"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
811 bytes sent via SQL*Net to client
395 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25 rows processed
AND HERE IS THE SAME QUERY WITH FOO=99 (99% OF THE ROWS):
select /*+ FIRST_ROWS(25) */ * from (select foo, rownum rn 2 from foobar where foo=99 order by bar) where rn <= 25;
25 rows selected.
Elapsed: 00:00:00.78
Execution Plan
----------------------------------------------------------
Plan hash value: 2407035716
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 650 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 25 | 650 | 3 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
|* 3 | INDEX RANGE SCAN| FOOBAR_IDX | 25 | 175 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=25)
3 - access("FOO"=99)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2624 consistent gets
0 physical reads
0 redo size
811 bytes sent via SQL*Net to client
395 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25 rows processed
In the first case there were 30 consistent gets and in the second - 2624!
Why?! Plan is the same. The index contains exactly the answer to the query
(both columns, sorted as requested), so I would expect it to just grab the
first 25 rows from the index and return them. I would ignore a few blocks
difference but it's not the case - Oracle is obviously doing something
different with those two queries.
Here is the output from tkprof:
********************************************************************************
select /*+ FIRST_ROWS(25) */ * from (select foo, rownum rn
from foobar where foo=1 order by bar) where rn <= 25
call count cpu elapsed disk query current rows
------- ------ ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 30 0 25
------- ------ ------ -------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 30 0 25
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 36
Rows Row Source Operation
------- ---------------------------------------------------
25 VIEW (cr=30 pr=0 pw=0 time=70 us)
10000 COUNT (cr=30 pr=0 pw=0 time=60049 us)
10000 INDEX RANGE SCAN FOOBAR_IDX (cr=30 pr=0 pw=0 time=30038 us
(object id 14207)
********************************************************************************
select /*+ FIRST_ROWS(25) */ * from (select foo, rownum rn
from foobar where foo=99 order by bar) where rn <= 25
call count cpu elapsed disk query current rows
------- ------ ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.75 0.75 0 2624 0 25
------- ------ ------ -------- ---------- ---------- ---------- ----------
total 5 0.75 0.75 0 2624 0 25
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 36
Rows Row Source Operation
------- ---------------------------------------------------
25 VIEW (cr=2624 pr=0 pw=0 time=73 us)
990000 COUNT (cr=2624 pr=0 pw=0 time=5940050 us)
990000 INDEX RANGE SCAN FOOBAR_IDX (cr=2624 pr=0 pw=0 time=1980043 us
(object id 14207)
Seems like Oracle cannot optimize the query and really reads all those rows
instead of stopping after the first 25. Is it true or am I missing
something? I can't believe that with all those tricks that CBO is capable
of, it can't do such an obvious one. Any suggestions?
Thanks!
--
Michal Kuratczyk
|
| | 4 answer | Add comment |
|
| ORA-00920 error on simple sql query ! Samir.Vds@Googlemail.Com 20:02:39 |
| | Hey,
I just started my first job last week.
Anyway, I want to run the following sql query on an Oracle database:
select client , kunde , kunden_name , projekt , projekt_name ,
angebot , auftrag , bestell_betrag ,
bestell_datum ,
erloese , konto , konto_name , buch_periode ,
buch_datum
, beleg , lstart , projekt_ende ,
projekt_erstellt ,
projekt_status
from HAUFB0363
where ( bestell_betrag - erloese ) <> 0
I get the error message: ORA-00920 invalid relational operator.
But with my SQL tool called "TOAD" the query works fine !
I'd appreciate any help
Samir
|
| | 4 answer | Add comment |
|
| Crystal Reports csv including report detail R Hall 18:26:44 |
| | Hi, I have a report that I wish to export as a csv, the problem is that
when it is exported, it includes all the detail despite the fact it is
supressed, within the csv when I just need the totals. Have anyone any
idea why this is, and how I can fix this please?
|
| | 3 answer | Add comment |
|
| Oracle Compile Question........ Amerar@Iwc.Net 17:33:42 |
| | Hi All,
We are running Oracle 9.2.0.7. We have a number of jobs running in the
queue. A few things have become an issue here.
First, if we need to re-compile a stored procedure that is being used
by one of the jobs, the job must first be stopped. Strangely enough,
even if we want to issue a simple grant to a table or on a procedure,
we cannot as long as the object is being used......?? Why???
However, even if we use DBMS_JOB.REMOVE to remove the job from the
queue, for some reason the JQ locks are never released, and thus I
cannot comple or grant. I need to manually go and kill the
session........
Any thoughts or ideas?
Thanks!
|
| | 1 answer | Add comment |
|
| Recovery need help ... urgent. Guest 17:28:10 |
| | Hello,
We have following scenario.
- database backup 3 weeks old
- all archivelogs from that time, but one about 2 weeks old is damaged
- database export 1 week old
So with regular recovery we are not able to recover database since 1
redo is unusable.
Is there any option to recover database using 1 week old export and
than apliyng rest of archlogs which are not older than export and all
archlogs are complete from that time.
Thanks a lot for answers
Robert
|
| | 3 answer | Add comment |
|
| Re: It is time for eXtreme Garbage! DA Morgan 14:20:12 |
| | XG is a new acronym. It stands for: eXtreme Garbage
Garbage offer
Garbage software
Garbage mouth
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
|
| | 10 answers | Add comment |
|
| Installation of 10.2.0.1 on Solaris 10/06 x86-64 Thorsten Jens 14:00:10 |
| | Hi,
has anybody installed 10.2.0.1. on Solaris 10/06 x86-464 yet? I tried
to do so in an ssh session, with the DISPLAY forwarded to my PC:
--------------------------------------------------------------------------------
oracle@nus001-rman ~/install-tmp/database $ ./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be 5.10. Actual 5.10
Passed
Checking Temp space: must be greater than 250 MB. Actual 1832 MB
Passed
Checking swap space: must be greater than 500 MB. Actual 2045 MB
Passed
Checking monitor: must be configured to display at least 256 colors.
Actual 32768 Passed
All installer requirements met.
Preparing to launch Oracle Universal Installer from
/tmp/OraInstall2006-08-09_12-19-06PM. Please wait ...oracle@nus001-rman
~/install-tmp/database $ Exception in thread "main"
java.lang.InternalError: Can't find supported visual
at sun.awt.X11GraphicsEnvironment.initDisplay(Native Method)
at
sun.awt.X11GraphicsEnvironment.<clinit>(X11GraphicsEnvironment.java:134)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:141)
at
java.awt.GraphicsEnvironment.getLocalGraphicsEnvironment(GraphicsEnvironment.java:62)
at java.awt.Window.init(Window.java:231)
at java.awt.Window.<init>(Window.java:275)
at java.awt.Frame.<init>(Frame.java:401)
at oracle.ewt.popup.PopupFrame.<init>(Unknown Source)
at oracle.ewt.lwAWT.BufferedFrame.<init>(Unknown Source)
at
oracle.sysman.oio.oioc.OiocOneClickInstaller.<init>(OiocOneClickInstaller.java:378)
at
oracle.sysman.oio.oioc.OiocOneClickInstaller.main(OiocOneClickInstaller.java:2091)
--------------------------------------------------------------------------------
I've searched for this on Metalink and Google, but found nothing. It's
not a general X forwarding problem, as /usr/openwin/bin/xclock works
fine.
Thorsten
|
| | 5 answers | Add comment |
|
| SELECT ... WHERE column LIKE parameter||'%' Heini Nolsшe 13:46:56 |
| | I have a database containing alot of procedures with this kind of coding logic:
BigTable
column1 varchar2(40);
Procedure SomeProcedure (in_parameter varchar2)
SELECT column1
FROM BigTable
WHERE column1 LIKE in_parameter||'%';
END;
The procedure is called like this:
SomeProcedure('abc');
Although this coding logic selects the correct rows, there is a big problem - it does not use the index on BigTable.
But if you do perform at little test and change the procedure like this:
Procedure SomeProcedure (in_parameter varchar2)
SELECT column1
FROM BigTable
WHERE column1 LIKE 'abc'||'%';
END;
Then it uses the index. But ofcourse now there is not the desired flexibility of using a parameter.
QUESTIONS:
1. Why does Oracle not use the index when a parameter is used in the LIKE-statement?
|
| | Add comment |
|
|
