PL/SQL :: Select On View Takes Long On Definition Short - No Difference In Exec-plans?
Aug 20, 2012
[URL]...
I have a quite complex view that selects from approx 10 long tables (approx 4M records each) and build one "customer sentence" pre customer id. I will be always getting just one row from this view, eg. select * from my_view where party_id = XYZ. I'll NEVER EVER select the whole view.
The problem is that running a query:
select * from my_view where party_id = XYZ takes really long time, while putting the party_id = XYZ condition directly into the view executes in 0.0 seconds.
After putting a ORDERED FIRST_ROWS(1) hint into a view the execution plans seems to be the same (or very similar) for both queries. Unfortunately, I can not transfer anything but screenshot from the environmnet - therefore I paste the exec plans as screenshots only - pls follow the link: [URL]...
View DDL:
create or replace view my_view as
select /*+ ORDERED FIRST_ROWS(1) */ pt.party_id
pt.party_id as id_klienta_mdm,
pt.master_reference_no as id_klienta_ref_mdm,
[code]...
View 6 Replies
ADVERTISEMENT
Apr 25, 2012
We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.
This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?
I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....
View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.
View 6 Replies
View Related
Feb 6, 2013
what's long connection and short connection in Oracle ?
View 4 Replies
View Related
Oct 8, 2012
A DB server (Oracle 11g) on Linux will support a Web Application Server (Java based) for hosting a public web site.For sending multiple INSERT statements over from the Java Server to Oracle Server, I have two ways to do it:
1) Wrap those insert statements together and send it over with one connection to Oracle
2) Send each insert statement separately to Oracle. As the result, multiple insert statements are sent and accordingly multiple connections to Oracle are needed.
Advantage of option 1 is just using one Connection. Disadvantages are that Oracle will then need to parse the statement string into multiple separate insert statements, and then loop through them to execute each. This may be costly in CPU sense.
While the option 2 may have quicker execute of each insert statement, but will need to create multiple connections.If the number of users is known and stable (such as intranet), I would prefer option 1; while for the internet there could hundreds concurrent users at the peak, I lean towards the option 2.
View 2 Replies
View Related
Jul 17, 2013
11.2.0.3 This is for a build. We are still in development. No risk of data loss. As part of the build, I drop the user,re-create it, re-create the objects. Allows us to test the build all the way through. Its our process. This user has some tables with several 1000 partitions. I ran a 10046 trace and oracle is using pl/sql to do loops to do DML against the data dictionary. Anyway to speed this up? I am going to turn off the recyclebin during the build and turn it back on. anything else I can do? Right now I just issue 'drop user cascade'. Part of is the weak hardware we have in the development/environment. Takes about 20 minutes just to run through this part of the script (the script has alot more pieces than this) and we do fairly frequent builds. I can't change the build process. My only option is to try to make this run a little faster.
View 3 Replies
View Related
Jun 22, 2012
MY DB Version: 10.2.o
OS: Windows Server 2003
I am trying to import on table which i have the export dump file which i take using expdp previously when i load that table on the same host
by using below command:
expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
after that i zip that dump and move it to external usb and now i need that table i copy that table and unzip that that dump
Command i am using to do the import is :
impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
But the query of import is still runing even not showing any amount of rows to be imported.
i already make the tablespace in which the table was previosuly before dropping but when i check the sapce of tablespace that is also not consuming one error i got preiviously while performing this task is:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "CDR"."SYS_IMPORT_TABLE_03" successfully loaded/unloaded
Starting "CDR"."SYS_IMPORT_TABLE_03": cdr/********@tsiindia directory=TEST_DIR dumpfile=CAT_IN_DATA_042012.DMP tables=CAT_IN_DATA_042012 logfile=impdpCAT_IN_DATA_042012.log
[code]....
i check streams_pool_size it will show zero and then i make it to 48M and after that
SQL> show parameter streams_pool_size;
NAME TYPE VALUE
-----------
streams_pool_size big integer 48M
But still it takes time
View 13 Replies
View Related
Nov 28, 2012
I have a view in schema 'sales', let say the view name is view_test.
This view sales.view_test uses two base tables, one from schema 'products' and other from its own schema 'sales'.
So in sales schema the view defination is,
create or replace view view_test
as
select * from t_test, products.t_products
where t_test.id=t_products.id
The view got created, I have granted select permisson with grant option on tables t_test, products.t_products and view_test to another user 'master'.But when i query the view from master schema I get an error saying that the view does not exists, upon analysis, I found one of the base table in the view defination has no schema name mentioned (select * from t_test, products.t_products --here only t_test mentioned rather sales.t_test), I feel because the table t_test belongs to the same schmea of the view, they did not mention the schema name. If not, does it mean that we need to always specify the schema names in the view defination for the base tables.
View 6 Replies
View Related
Jun 26, 2008
I am using Pro*C/C++ Release 10.2.0.2 in HP-UX. But Pro*C/C++ application was written long back during oracle release 8. Now we are facing a problem like EXEC SELECT query is not fetching the records even though record is present in DATABASE.
This is not happening every time. This problem starts happening only after heavy use of the unix process.
For every request, unix process will fetch the record and updates the same at the end and process goes to wait mode to get the request again. Let say after 50 request, process is returned with no rows found error.
It started working fine only after restarting the process and problem starts again after 50th or 60th request. This problem we are facing only after upgrade to 10g.
View 1 Replies
View Related
Dec 6, 2012
We're using Oracle 10g for development purposes. I have 2 same schema with approximately same data. I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thought first that there may be missing constraints or indexes but all are the same.
I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
what should I do to fix the problem with the NAFBCA schema.
Attached File(s)
NAFBCA.JPG ( 57.49K )
Number of downloads: 6
NAFBCA2.JPG ( 60.36K )
Number of downloads: 4
View 1 Replies
View Related
Dec 6, 2012
We're using Oracle 10g for development purposes.I have 2 same schemas with approximately same data.I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thoughtfirst that there may be missing constraints or indexes but all are the same.I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
View 19 Replies
View Related
Sep 7, 2012
How can I determine what views have a dblink hard coded in them? I am talking about a large number of views so bringing up each view's DDL in a GUI data dictionary tool to look is not an option. I tried running the query below unfortunately "text" is a LONG and doesn't allow the use of the "like" statement.
select * from dba_views where text like '%@%' ;
View 4 Replies
View Related
May 20, 2013
How to retrieve a view definition to re-create it somewhere when its size is more than 32767 bytes?Here are some constraints :
- We are in Oracle 9.2.0.8, meaning that we cannot use the DBMS_METADATA package.
- We cannot concatenate char and long : things like
set long 100000
select 'create or replace view my_view as ' || text from dba_views where view_name = 'MY_VIEW';
are not possible.
View 31 Replies
View Related
Feb 22, 2013
I created a view for emp1 table.
SQL> select view_name , text from dba_views where VIEW_NAME='EMP1_VIEW';
VIEW_NAME TEXT
EMP1_VEW select empid , ename , qual, dept from emp1
where empid between 1000 and 1010
Now i tried to use create or replace option
SQL>create or replace view emp1_view as
*2 select * from emp1_view with read only;*
create or replace view emp1_view as
*+
ERROR at line 1:
ORA-01731: circular view definition encountered
Actually i want to make read only view (emp1_view) ; without doping ;
View 16 Replies
View Related
Apr 26, 2011
difference between view and materialized view?
View 1 Replies
View Related
Aug 12, 2013
what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?
View 7 Replies
View Related
Feb 13, 2013
I am getting error (ORA - 00972 : identifier too long) while creating the view.
create or replace view ELVW_ATM_REC_HANDOFF_1(Rectype,Recseq,Record_Type,Record_Sequence,MESSAGE_TYPE,PAN,PROCESSING_CODE,TRANSACTION_AMOUNT,TRANSACTION_CUR RENCY_CODE,SETTLEMENT_AMOUNT,SETTLEMENT_CURRENCY_CODE,Billing_AMOUNT,Billing_CURRENCY_CODE,Transaction_DATE,Settlement_Date,TRACE,REF
[code]...
i have checked the length of the column name. Its less than 30 characters
View 4 Replies
View Related
Nov 23, 2011
An user is running some queries and that's making database hang. I want to view what queries this user is running. Can this be done by session auditing turning on?
View 13 Replies
View Related
Sep 4, 2013
I have long select which operate on 5 tables and has a lot of conditions in where clause (many combinations of values of just a few columns). Does reducing of those conditions could improve performance or just has a small impact?
I think if I have a lot of conditions on the same column, it don't take a lot of time to check them because values are in memory.
View 3 Replies
View Related
Jun 21, 2011
If i try to find length of the string with more than 4000 char in SQL Developer it throws error "ORA-01704: string literal too long". if anything i need to SET in preference.
select length('string with more than 4000 char) from dual;
View 10 Replies
View Related
May 3, 2010
I want basics of snapshot...creation,deletion,check...etc and what main difference in snapshot and view..?
View 2 Replies
View Related
Jun 24, 2010
What is the difference between Materialized view and dblink. When we use materialized view and when we use dblink. In my knowledge mviwes and dblinks are used to retrieve data from remote database.But I am unable to understand when should we materialized views and when should we use dblinks.
View 1 Replies
View Related
Aug 29, 2012
the difference in detail with an example.
View 5 Replies
View Related
Jul 7, 2008
what is the difference between materialized view and snapshot ?
View 1 Replies
View Related
Jul 7, 2012
After searching a lot on net the differences between inline view and subquery that i came across are
1) you can use order by in inline view but not in subquery.
2) you can use alias for inline view but not subquery.
i have to fetch the same outcome using both the functionality respectively, then what is the technical impact/better.
View 4 Replies
View Related
Aug 29, 2012
just i want add diiferece column in my select statement.For Example:-
select
Name,
Salary,
Designation,
date_first_payment,
date_last_payment,
(date_first_payment-date_last_payment) Datediff
from My_table
then its working
select *,(date_first_payment-date_last_payment) Datediff
from My_table
its not working..
View 7 Replies
View Related
Aug 21, 2008
I have 3 sites which want to be updated as soon as the other one updates its data.(in 5 seconds). So I have chosen multi master solution,sync regardless of the resource consideration.
1-what is the difference between multimaster async and updatable materialized view regardless of resource consuming in multimaster?because in both of them each site can receive and also propagate the data...I just know that in materialized view we can work offline and then apply it manually is there any other thing?
2-I can not understand the difference between multimaster sync and async (considering the 5 seconds,because 5 second is a little time)I mean in this case are they different from each other or not.
View 1 Replies
View Related
Oct 21, 2012
I am confused with third one.
CODE1. >> Comparing two strings >>
SQL> select greatest('99' ,'100') from dual;
GR
99
2. >> comparing both numbers >>
SQL> select greatest( 99 ,100) from dual;
GR
100
3.>> Comparing string and number >>
SQL> select greatest('99' ,100) from dual;
GR
99
whats the logic behind 99 being returned for thirdone.
View 3 Replies
View Related
May 14, 2010
I am using embedded sql in a Fortran program on IBM AIX. Whenever I execute a SQL statement other than connect I get a coredump. So I wrote a small test program, but same thing happens. This is the test program -
PROGRAM TEST
EXEC BEGIN DECLARE SECTION
CHARACTER*20 UDPWD
EXEC END DECLARE SECTION
[code]....
And then when I run test, I see the 'Connected' print & then coredump. Same thing happens for EXEC SQL Prepare statement. Am I not linking some library ?
View 1 Replies
View Related
Jun 11, 2012
So this statement works:
SELECT column_name BULK COLLECT INTO table_column_list FROM all_tab_columns WHERE table_name = conv_tablename AND OWNER IN (SELECT USER FROM DUAL);
However I want to add conditions like " AND column_name <> 'abc' " at the end. So i tried to store this in a varchar and call EXEC on it:
select_column_value_sql_stmt := 'SELECT column_name BULK COLLECT INTO table_column_list FROM user_tab_columns WHERE table_name = '''|| conv_tablename || '''' || inexclude_column;
EXECUTE IMMEDIATE select_column_value_sql_stmt;
No matter how I concatenate the conv_tablename part, i get an error. either invalid identifier or unimplemented feature.
so is there a way to do this at all?
View 3 Replies
View Related
Dec 22, 2012
I got an exception when I was using sesame adapter to dump a turtle file which contains long texts as objects into oracle semantic database. The exception information is:
org.openrdf.repository.RepositoryException: org.openrdf.sail.SailException: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: in "SF.ORACLE_ORARDF_ADDHELPER", line 1
ORA-06512: in line 1
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) ...
View 1 Replies
View Related
