There is another small suggestion. You are using correalted subqueries in your code. You can override them also in your code. For reference, you can see this site:
http://asktom.oracle.c om/pls/asktom/f?p=100:11:0::N O::P11_QUESTION _ID:30832869708 77...
Medhatithi
Last Activity: Aug 13 '07, 09:58 AM
Joined: Mar 6 '07
Location:
-
SET STRING_RTRUNCATION ON in Sybase
Hi, I am using Sybase ASE 12.5. I just learnt about the STRING_RTRUNCAT ION option. When, I am setting this option ON, then it is doing fine inside a session. But, when I am doing an insert from inside a stored procedure, then this option does not seem to work. Sybase server is automatically truncating the values and inserting into the columns instead of throwing any errors. Can anyone help on this how to set this option at the procedure level. I... -
Hi all, found a solution after going thru the docs a bit. There is an option
SET STRING_RTRUNCAT ION ON, which will override the default behaviour. Howevre, this is a session level statement, but it does not seem to work while inside a proc, an insertion is doneLeave a comment:
-
Insertion automatically truncates the value
My table creation script is as follows:
CREATE TABLE BSClient(BSClie ntUniqNum int ,
CompanyName varchar(15));
Now, when I am inserting a row like this:
insert into BSClient values(121,'GEN ERAL ELECTRIC');
Here, the company name is 19 characters long. Ideally, this should give an error (at least in Oracle, it does). But, here the string is implicitly... -
There is however, a very important point regarding decode. It automatically converts the second return value to the datatype of the first return value. And if the first return value is null, then the second return value is converted to varchar2. BE VERY CAREFUL USING DECODE FUNCTION IF THE FIRST RETURN VALUE IS NULL i.e.
max(decode(stat us,'BC',NULL,cr eate_date))
In this case, the create_date column will be converted to varchar2 type,...Leave a comment:
-
I don't have these privileges, actually. That's what I meant by saying "I don't seem to have proper rights to view the TRACE". Are there any other way-outs? For example, in Sybase you can do so, by pasting a pl/sql block and then running the explain plan for the whole block...Leave a comment:
-
Hi, I have solved the problem and also mentioned it in the post. You can follow the chain for that purpose. Actually, the problem occured, as for a decode function, if the first return type is null, then the second result type is implicitly converted to varchar2 type. So, I HAD TO USE THE TO_DATE FUNCTION IN PLACE...Leave a comment:
-
This may be possible due to using the nested table in the join. You can use the CAST and then the DYNAMIC_SAMPLIN G hint to override this behaviour...Leave a comment:
-
There are however, certain limitations for using this package. You must go through them in Oracle documentation to use it. Alternatively, you can use SUBSTR & INSTR to separate the values. I can give you a sample code:
declare
lc NUMBER(10):=1;
v_loanlen NUMBER(10);
v_len NUMBER(10);
v_start_sub...Leave a comment:
-
There is however, a very important point regarding decode. It automatically converts the second return value to the datatype of the first return value. And if the first return value is null, then the second return value is converted to varchar2. BE VERY CAREFUL USING DECODE FUNCTION IF THE FIRST RETURN VALUE IS NULL i.e.
max(decode(stat us,'BC',NULL,cr eate_date))
In this case, the create_date column will be converted to varchar2 type,...Leave a comment:
-
I can suggest a way to locate the pain area first. You can create a database table instead of the global temporary table and run the same package, now inserting into this datbase table. Then it would be evident if the time taken is due to insertion in a Global Temporary Table specifically or not.
Also, I can share one of my experiences with global temporary tables, may be it can give you any clue. My query joining a global temp table was taking...Leave a comment:
-
I don't have any clues, but I request if you get any solution to this problem, can you please let me know. I am very interested about this...Leave a comment:
-
Hi debasis,
Suppose I have a procedure which contains several select into statements. How can I see the explain plan for these individual select into statements when I execute the procedure? I don't seem to have proper rights to view the TRACE, but for individual sql statements, I find out the explain plan by seeing the plan_table, but for select statements executed inside a procedure, what should be my approach?...Leave a comment:
-
To access other user's objects, you must specify the schemaname.obje ctname. Or lese you can ask your DBA to create a synonym on that objetc and grant PUBLIC...Leave a comment:
-
Tom Kyte said about it correctly, there was a version where it did make a difference (6, early 7 maybe) and thus a myth was born. According to Tom, the parser is now coded so that count(1) and count(*) use exactly the same code path so any differences you see between the two would occur on multiple runs of the same statement, i.e. they are caused by external events....Leave a comment:
-
In fact, if we use DML Operation inside a function, then this function cannot be used in the select clause of any query...Leave a comment:
-
Yes, that is alright. Actually, my main concern was to use indexes, which is not possible if I operate to_char on the date column .....Leave a comment:
-
In implicit conversions between char & varchar, are indexes used?
I have a table whose indexed column is a char(16) field. I am giving the schema.
CREATE TABLE Emp(Empno char(16),
Ename varchar(25),
Salary number(10,2),.. ....)
Now, there is an index on the Empno field. Now, inside a stored procedure, I am executing a statement
select Name=Ename from Emp where Empno=@eno
This @eno is varchar(25).... -
I can give you a small suggestion, though I'm not sure this will help or not.
In your query, there is a clause:
and to_char(d.ledg_ trandate,'dd/mm/yyyy') = (select to_char(max(e.l edg_trandate),' dd/mm/yyyy')
Here, is there any index on the d.ledg_trandate field? In that case, this query will not use it. Just try to avoid the to_char function on the date field to enable Oracle CBO to use the index...Leave a comment:
-
SQL Tuning tricks
Hi,
I have been in several ways benefiited from this site. I would like to share some sql tuning techniques(simp le, but effective) with you all.
SQL Tuning Tips
Oracle Tips Session #6
3/31/98
Kathy Gleeson
_______________ _______________ __________
SQL is the heart of the Oracle system. You can use many different SQL statements to achieve the same result. It is often the case that...
No activity results to display
Leave a comment: