SQL & PL/SQL :: Pipe Line Function Taking Time To Return Table Record

Mar 15, 2011

I want to use a function in join clause. so i go for pipelined function(using for loop to get record & 1 more loop to fetch in table type variable). i achieved what i required. but problem is it takes much time to fetch data. is there any other approach which returns table records without pipelined function.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Split Is A Pipe Line Function To Convert Row As Columns

Jan 31, 2013

In this query split is a pipe line function to convert row(rows stored with , delimited) as columns like below

for ex for below query
SELECT * from TABLE(SPLIT('bbb003,bb004'));

out put is
bbb003
bb004

now i have to apply same function on column,column is storing data with ',' separated.and i have tried like but it's throwing missing expression. how i can use this function on entire column from this table.

SELECT * from TABLE(SPLIT(select candidates FROM ibis.cw_uploads_inprogress ));

View 7 Replies View Related

SQL & PL/SQL :: Parallel In Pipe-lined Table Function Not Working?

Feb 1, 2011

'Oracle fast parallel data unload into ASCII file(s)' in this blog: URL....I have compiled the code and created the objects and the directory in my DB...But when I execute :

SELECT *
FROM TABLE(
DATA_UNLOAD(
CURSOR(
SELECT /*+ PARALLEL(A, 2, 1) */
TABLE_NAME || '|' ||
COLUMN_NAME || '|' ||
DATA_TYPE
FROM MYTABLE A
[code]....

It is supposed to return 2 rows (because of parallel execution), but it just returns 1..Do I have to do something special in order to make parallel pipelined function work

View 2 Replies View Related

Table Has No Rows - Select Taking Time

Aug 23, 2010

We were trying to insert approx 76 million records worth of approx 4 GB in a table when the insert operation failed with the archive log error.

The database was hanged, we re-started the database and currently there are no records in the table but when we are firing a select

select count(*)
from table

It's taking approx 2 mins to come out with the result.

We checked and found that there are no locks on the table currently.

what do we need to do to get the performance back

View 5 Replies View Related

Drop Statement On Table Taking Long Time

Sep 20, 2010

We are firing a normal Drop command on our database and the database version is 10.2.0.4.The database is running on AIX v5.The command is taking more time than usual .

When i am monitoring the session i can see that a call is being made to procedure "aw_drop_proc".Could i ask you if this is something that is taking more time than usual.

We are not having any partitions on the nested tables .We have a pack of tables and we are dropping this pack through a procedure.The pack comprises of nested tables & normal tables.To drop a nested table it is taking around 6 seconds(Table with no rows) and a normal table(With no rows) it is taking 17 milli seconds.We have a partition on Normal table.

The same operation in windows is taking very less time when compared to AIX.

View 5 Replies View Related

PL/SQL :: Table Access By Index Rowid / Taking More Time

Sep 13, 2012

I've a query like

update tab1
  set col1 = ( select col2 from
                       tab2 
                where tab1.id = tab2.id) table 1 has arnd 10,000 rows

table 2 has arnd 1,700,000 rows and has a primary key on column id.This query is taking around 20 secs to execute. I checked the x-plan and most of time taken for table access by index rowid.I checked the stats for the tab2, its just three days old.

View 17 Replies View Related

Pipe Function And Materialized View

Feb 27, 2011

Pipe Function and Materialized View.

There is a materialized view:

CODECREATE MATERIALIZED VIEW MY_MAT_VIEW NOLOGGING
REFRESH FORCE
START WITH SYSDATE NEXT SYSDATE + 5/24/60
AS
SELECT F1,
F2,
F3
from SOMETAB;

And there is a pipelined function:

CODEDROP TYPE MY_MAT_VIEW_table_type;
DROP TYPE MY_MAT_VIEW_row_type;

CREATE TYPE MY_MAT_VIEW_row_type AS OBJECT
(
F1 number,
F2 varchar2(100),
F3 date
[code].......

I've noticed that when materialized view is near to be refreshed (every 5 minutes) there is some "delay" in getting result sets using this query:

SELECT *
FROM TABLE(FN_GET_MY_MAT_VIEW(100)) a;

And when the refresh is finished the result set returns immediately.

Question.

Is there a way to avoid such "delay" while materialized view is refreshing?

View 1 Replies View Related

SQL & PL/SQL :: PIPE Multiple Rows At Once In PIPELINED Function?

Sep 13, 2012

I've used PIPELINED FUNCTION and I've no issues in using this. Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.

Like we use BULK COLLECT INTO to fetch multiple rows at once instead of fetching one row using SELECT INTO.

Below is the test case:

CREATE TABLE TMP_EMP
(
EMP_ID NUMBER(10,0),
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER(10,0),
SALARY NUMBER(14,0),

[code]....

View 13 Replies View Related

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

Aug 30, 2011

I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.

Explanation:

In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.

I need to query on this dynamic table and return the result as return result. But i am not able to do it.

Here T_web_loylty_report_table is a type.

CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......

View 2 Replies View Related

SQL & PL/SQL :: Return Ref Cursor To Temporary Table From Function

Feb 17, 2011

I have strange problem when i try to return a ref cursor holding data from a select on a oracle global temporary table. If i iterate through the cursor , i can see the values but the function as such returns nothing through the ref cursor. I tried the temporary table as both delete on commit and preserve on commit

create or replace
PACKAGE BODY BILL AS

FUNCTION FILTERI RETURN BILL.refcursor IS
testcursor BILL.refcursor;

ttstatus INT;
iSuccess INT;
returns INT;
TruncatedSQL1 VARCHAR2(32767);
BEGIN
[code].........

View 12 Replies View Related

PL/SQL :: Create Function To Return Table Type

Jul 25, 2012

I am trying to create a function which would return a nested table with 3 columns of a table as a type.

my query is like

select col1,col2,col3 from table_1;

View 4 Replies View Related

Performance Tuning :: Query Is Taking More Than 5 Minutes To Return Data For Any Criteria

Aug 17, 2011

The below query is taking more than 5minutes to return the data for any criteria.The big tables are

SECURITY_POSITION_SUMMARY -- 60Million
WEB_TEAM_X_ACCOUNT_BM -- 26Million

and the rest of those are small tables..All the indexes are in place and I have tried with few hints but this query is slow.

WITH REPS
AS (SELECT DISTINCT REP_SET.FILTER_TOKEN
FROM (SELECT /*+ INDEX (wdsd WEBDATASETDTL_PK_TEAM) */
DISTINCT
WDSD.DATA_SETTING_ID, WDSD.FILTER_TOKEN
FROM WEB_DATA_SETTING_DETAIL WDSD,
[code]....

View 1 Replies View Related

SQL & PL/SQL :: Carriage Return Line Feed Multiple Times In Same CLOB

Jan 31, 2012

I'm looking for a way to make CRLFs show in a CLOB.I'm feeding the insert statement with a concatinated string like this:

insert into table(Data) values (MyConc)

(MyConc is a string put togheter by another application)

Because of this I can't use the "|| chr(13) || chr(10) ||" because I only have that one concatinated string.Is there a character I can set in my string that automatically translates to a CRLF?

My CLOB-data should look something like this:

1;blue;Woodstock;;
34;giant;squid;attack;
5;blue;squid;;

And in this case the "MyConc" would look like this "1;blue;Woodstock;;[X]34;giant;squid;attack;[X]5;blue;squid;;[X]

where [X]=the character I need for CRLF =)

View 11 Replies View Related

SQL & PL/SQL :: Find The Time Of A Record Inserted Into Table

Oct 20, 2010

I have a table which is getting populated from a source. I dont have a time-stamp column in the table. Is there anyway to find the time when a record was inserted into the table?

View 3 Replies View Related

SQL & PL/SQL :: CLOB Parameters Taking More Time

Mar 3, 2012

We are on a GRID DB and using Oracle 11g.

A external reporting application ( SQL SERVER REPORT SERVICES) sends in some comma separated parameter values, which has to be queried against a table with 6-10 million records.

Length of the comma separated value can go upto 5000-6000 in length based on user input in the front end application. This application sends this value in comma separated. i.e., like

'AA1-11101,AA2-34346,AA4-534399,.....' like this at a time the application can send upto 500 values each of length 10. i.e, maximum length can be upto 5000. I used CLOB to handle this because since the length can be 5000 and varchar2 can handle only 4000 long literals.

But the time taken by the CLOB to verify against the table using INSTR is more compared to VARCHAR2.

I found that VARCHAR2 does'nt take much time. Is it a good idea to have VARCHAR2 in the PLSQL procedure as parameter instead of CLOB, since PLSQL VARCHAR2 can handle upto 32000 long values.

View 10 Replies View Related

SQL & PL/SQL :: How To Get Which Query Is Taking Time While Executing

Mar 2, 2013

How to find time log for query or any procedure like start time and end time and total time.

So that I can tune that queries properly.

Also how can we find estimated query running time.?

View -1 Replies View Related

SQL & PL/SQL :: How To Find The Query Which Is Taking Most Time

Jul 4, 2011

I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.

View 2 Replies View Related

SQL & PL/SQL :: Same Query Taking Different Time When Executed Through Different DBs?

Feb 19, 2013

I have a query which is executing fast in dev env,but very long time in qa env.What is the criteria when this behaviour occurs.Though qa is having more data than dev.But still it is taking long time for 1 rows also.When I am using the query rownum<=1.So What to check for this.

View 6 Replies View Related

Performance Tuning :: Sql Taking More CPU Time?

Nov 23, 2010

I have one query in my production which is taking more CPU time. when that statement executing the CUP is taking more than 90%

I am attaching the sql query and indexes on the table.

View 4 Replies View Related

Forms :: Show_view Taking So Much Time In 10g

Mar 21, 2011

I have one big form where the first canvas (content) is taking nearly 20 seconds to display. this is in web mode using forms 10g.

i have tried using SHOW_VIEW(find_view('SAMPLE')) but still no improvement.

View 5 Replies View Related

Block Of Procedure Taking Time To Execute

Feb 9, 2011

I have connected to the database from two sessions

Box 1 : i have executed a procedure.it took few seconds to get executed

Box 2 : I have executed a block of procedure.It is taking 1 min 40 secs to get executed.

why it is taking much time from Box2(for executing a block from procedure) , as the same code executed successfully in a procedure from Box 1.

View 2 Replies View Related

Oracle 11.2.0.3.0 - Select Query Taking Time

Jan 24, 2013

My oracle database version is 11.2.0.3.0 where i am having one schema in that schema i am having 3 same tables with same structure same data but with different name.

but problem is in first table when i perform select query it takes 5 sec, in another table it is taking 0 sec and in third table it is taking 10 sec.

View 1 Replies View Related

Forms :: 10.1.2.0.2 Taking More Time To Load On Some Machines

Mar 31, 2010

Some of my forms taking to much time while loading on some machines but same forms working fine on other machines with same configuration.

Forms, version: 10.1.2.0.2, Clients: Win_XP_Pro, App Server: Window 2003 Server, IE7, Jinit 1.3.1.26

View 3 Replies View Related

SQL Queries Taking Long Time After 11g Data Migration

Sep 14, 2010

I have upgraded oracle database from 9i to 11g using export and import utility. After migration we are facing performance issue in report generation, We have observed that First execution of report is taking very long time and when we generate the same report 2 -3 times there is considerable change in the execution time and it is more better than the first execution.

2 days back I have restarted the database and found the same issue. There are around 300 Reports and it is not possible to generate all the reports 2-3 times every time we restart the database.

View 5 Replies View Related

Log Applying Service Is Taking More Time In Physical Standby?

Sep 19, 2013

My Database version as followsOracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - ProductionCORE  10.2.0.4.0  ProductionTNS for Linux: Version 10.2.0.4.0 - ProductionNLSRTL Version 10.2.0.4.0 - Production We have datagaurd setup as well - Huge archive logs are generating in our primary database - Archive logs are shipping to standby with no dealy - But applying the archive logs are taking more in our physical standby database -why it was taking more time  to apply archivlogs (sync) in standby ? - What could be possible reasons..? Note : Size of standby redo logs are same as redo log file of primary database - Also standby by redo one or more than online redo log primary. Since i need to report my higer leve stating this is cause for delay in applying archive logs.

View 32 Replies View Related

Inserting Timesten Cache Is Taking More Time Than Insert In Db11gR2?

Jan 20, 2011

We are inserting data using JDBC (Java program) in Oracle 11gR2 DB and Timesten to oracle (using AWT cache group) . In reality insertion in oracle is faster than Timesten Cache DB.

Timesten version - TimesTen Release 11.2.1.7.0 (64 bit Linux/x86_64)

1. It is Client/Server Model.
2. Cpu has 4 core and we are using 3 core for insert the Data.
3. Perm and Temp size is big enough compare to Data Size
4. auto commit=0
5. durable commit=0
6. PassThrough=1
7. LogBufParallelism=3
8. LogPurge=1
9. LockWait = 0.1

View 3 Replies View Related

Performance Tuning :: Delete Statement Is Taking More Time For Execution?

Mar 9, 2010

In my code I am using delete statement which is taking too much time to execute.

Statement is as follow:

DELETE FROM TRADE_ORDER_EMP_ALLOCATION T
WHERE (ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT)
IN (SELECT ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT
FROM LOAD_TRADE_ORDER
WHERE IND_IS_BAD_RECORD='N');

Tables Used:
oTRADE_ORDER_EMP_ALLOCATION Row count (329525880)
oLOAD_TRADE_ORDER Row count (29281)

Every column in "IN" clause and select clause is containing index on it

Every time no of rows which to be deleted is vary (May be in hundred ,thousand or hundred thousand )so that I am Unable to use "BITMAP" index on the table "LOAD_TRADE_ORDER" column "IND_IS_BAD_RECORD" though it is containing distinct record in it.

Even table "TRADE_ORDER_EMP_ALLOCATION" is containing "RANGE" PARTITION over it on the column "ARTEMIS_SOURCE_SYSTEM_ID". With this I am enclosing table scripts with Indexes and Partitions over it.

way for fast execution in of above delete statement?

View 4 Replies View Related

Performance Tuning :: Cost Of Query Is Brought Down But Taking Same Time

Sep 1, 2010

For an query, cost was 16Lakhs and was taking 30min, I brought down the cost to 1.5lakhs, but still it is taking 30min.

There were many outer joins and same table has been Used(FROM clause) 5 times in the query. I have introduced WITH clause, and brought down the cost.

View 7 Replies View Related

PL/SQL :: Normal Insert Proc - Bulk Collect Taking More Time

Feb 4, 2013

I am working on oracle 11g...I have one normal insert proc

CREATE OR REPLACE PROCEDURE test2
AS
     BEGIN
     INSERT INTO first_table
     (citiversion, financialcollectionid,
     dataitemid, dataitemvalue,
 [code]....

I am processing 1 lakh rows.tell me the reason why bulk collect is taking more time. ? According to my knowledge it should take less time. do i need to check any parameter?

View 5 Replies View Related

PL/SQL :: Stored Procedure Taking Long Time When Updates In Loop

Feb 5, 2013

This is my stored procedure

I have below store procedure:

create or replace
PROCEDURE TESTPERFORMANCE (
o_statuscode OUT NUMBER,
o_statusdescription OUT VARCHAR2,
starttime out timestamp,
time_after_query_TESTJOB out timestamp,

[Code]...

This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records. How to reduce execution time.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved