Oracle Forum RSS Feed (Replies Included)

Slow running query doing full table scan

Sat, 18 Aug 2012 07:09:23 -0700

I’m Using Oracle 11.

I desperately need help with a query that is running too slow - it's a very simple problem. I have a table with 16 million rows and an index (let's call it the employee table with an index on department). I need to select all the employees whose departments are located in the uk. I achieve this by selecting all the department numbers from departments where location = 'UK' in a sub select then plug this into the main query as follows:

SELECT *
FROM employees
WHERE department IN (SELECT department from departments where location = 'UK');

It takes ages, 25 seconds or more, the explain plan shows its doing a full table scan on emplyees. I need it to use the index. The sub query is instant and returns only 5 rows. If I explicitly put the 5 numbers in the IN clause the query uses the index and executes in 0.04 seconds. See below:

SELECT *
FROM employees
WHERE department IN (1,2,3,4,5);

This is so frustrating, please can anyone help, I need it to use the subquery once and then use the index on the main table.

Many thanks.

Slow running query doing full table scan

Sat, 18 Aug 2012 07:06:31 -0700

Re: How to display Oracle version currently running?

Fri, 20 Jan 2012 03:13:42 -0700

Try this.

Select * from v$version;

Count rows without SQL%ROWCOUNT

Sun, 18 Dec 2011 01:41:39 -0700

Hi,

I need to calulate the number of rows updated and/or inserted in a table without using SQL%ROWCOUNT ? Is there any record level statistics that could determine whether a row is updated or inserted ? Basedon rowid we can find the number of records inserted perhaps. But what about updations ?

Thanks

This post has been deleted.

Sun, 18 Dec 2011 01:16:16 -0700

This post has been deleted.

Re: Query Optimization

Tue, 17 May 2011 00:25:24 -0700

SELECT emp, salary, AVG(salary) OVER() "average salary" FROM empl;

Query Optimization

Sat, 14 May 2011 06:15:03 -0700

Hi,

I have table like

emp salary
--- -------
emp1 10000
emp2 10000
emp3 11000
emp4 13000
emp5 15000

I want avg salary in a separate column like

emp salary AvgSalary
--- ------- -----------
emp1 10000 11800.0000
emp2 10000 11800.0000
emp3 11000 11800.0000
emp4 13000 11800.0000
emp5 15000 11800.0000

I am getting the result using

SELECT emp, salary, (SELECT AVG( salary ) FROM empl ) 'average'
FROM empl

Can I do it using only one select statement.

Thanks,
Shrawan

Re: Oracle Reports to Excel file

Mon, 18 Apr 2011 15:58:03 -0700

I largest issue resides in the style sheets. No pun intended!

-Grahm
Excel Reports

Beginner seeking advice on Oracle and SQL, PL/SQL

Thu, 10 Jun 2010 16:14:39 -0700

Hello,

I'm completely new to databases but am looking to expand my knowledge for because I believe that the company I work for could benefit from the extra skill set.

My question is where to start my studies - I know I'll need to learn Oracle, SQL, and PL/SQL and I have a basic understanding of the Oracle to SQL and SQL to PL/SQL.

I've heard several theories about learning these - some say front to back (Oracle-SQL-PL/SQL) or work backwards starting with PL/SQL. Any thoughts or suggestions on this would be greatly appreciated.

Thanks again!

Re: never mind, I figured it out

Wed, 19 May 2010 00:56:31 -0700

Hi,

I am interested...
I hope you still know how you did it, 7 years ago

Re: Error in connecting Oracle 11g from MS Access

Thu, 13 May 2010 21:21:50 -0700

Hello,

I am trying to connect to my Oracle 11g database from MS Access query in .mdb file which is as below.

Select col1, col2, col3 From
[odbc;driver={microsoft odbc for oracle};server=ServiceName;uid=UserName;pwd=PassWo rd].[TableName]

But it gives me an error 'Reserved error (-7778); there is not message for this error'. Also, when i changed connection parameter (eg, uid or pwd etc), it will give me odbc error in connection. Does it mean that Oracle connection is done succesfuly, but there is any problem in fetching data from Oracle?

I have install 11g enterprise edition on my system winxp; and install ODBC and OLEDB providers with that.

I can run such query with Oracle 9i.

Did i do any mistake with Oracle 11g installation?

Please help.

Re: how to create database in oracle

Sun, 24 Jan 2010 07:37:29 -0700

to know that as we create schema in oracle and we store our database in that schema, in... The Database in Oracle ...cannot be created until the.

_______________
[url=http://www.microsoftoffice-2010.com/] Download Windows 7 [/url]

Re: Oracle Reports to Excel file

Sun, 24 Jan 2010 07:35:10 -0700

Rep2excel * An Oracle Report to Excel Converter ... Generate a report file in html or htmlcss format by oracle report server.

_________________
http://www.microsoftoffice-2010.com/

Re: analysis services and cluster algorithm

Fri, 13 Nov 2009 23:11:00 -0700

Hi,

ANALYSIS SERVICES: An integrated view of business data is provided by analysis service. This view is provided with the combination of OLAP and data mining functionality. Analysis Services allows the user to utilize a wide variety of data mining algorithms which allows the creation and designing data mining models.

SEQUENCE CLUSTERING ALGORITHM: Sequence clustering algorithm collects similar or related paths, sequences of data containing events.

E.g. Sequence clustering algorithm may help finding the path to store a product of “similar” nature in a retail ware house.

Yes, there are helpful material available online.
There is an E Book " THE MULTIDIMENSIONAL DATA MODELING TOLKIT"
available at DATA WAREHOUSING

analysis services and cluster algorithm

Fri, 13 Nov 2009 03:43:36 -0700

hi there,

i have recently started learning data warehousing
i am having doubt in analysis services and sequence
clustering algorithm, what is it??

and is there any helpful study materials available
online regarding this??

Re: how to create database in oracle

Mon, 12 Oct 2009 03:03:24 -0700

There are two ways that you can create a database in Oracle 9i. One way is to use the Oracle Database Configuration Assistant. Using the Oracle Database Configuration Assistant makes sure that the new database is optimized with Oracle 9i’s latest features. Simply read the instruction and follow the prompts. The other way to create a database is to do it manually. This will now be demonstrated.Create a suitable directory structure for the new database. Once this is done, copy the init.ora file into a new file named init{SID}.ora into new directory

Now you have created a parameter file, you’ll need to declare an Oracle SID name. Keep the SID consistent with the parameter filename. The command to do this action will depend on the platform you have the DBMS installed. If you are using Windows, simply type,

Set ORACLE_SID = {SID}

Alternatively, on the a unix platform, the DBA must type,

Export ORACLE_SID = {SID}

Use SQL*Plus under / as sysdba and set the database to nomount mode.

So, if we wish to override the default spfile to start the new instance in nomount mode then type,

Startup nomount pfile = ‘<directory>\init{SID}.ora’

Now we are about to create the database. When creating a new database, write the code into an sql file so that your have a copy of the database features as well as the ability to fix any mistakes in the create database command. Here is the code,

CREATE DATABASE ShepIT
Maxinstances 1
Maxloghistory 1
Maxlogfiles 10
Maxlogmembers 15
Maxdatafiles 100
Datafile ‘<directory>\datafile_01.dbf’
size 300M reuse autoextend on next 15M maxsize unlimited
character set WE8MSWIN1252
national character set AL16UFT16
logfile
group 1 (‘<directory>\redo_01.log’) size 75M
group 2 (‘<directory>\redo_02.log’) size 75M
group 3 (‘<directory>\redo_03.log’) size 75M
default temporary tablespace TEMP
tempfile ‘<directory>\temp_01.dbf’
extent management local uniform size 1M
undo tablespace UNDO_TS datafile ‘<directory>\datafile_01.dbf’
size 125M reuse autoextend on next 15M maxsize unlimited;

Oracle, should respond by saying that the database is created. Once this is done you are ready to load scripts that are needed to support your Oracle products by typing the following commands

@<directory>\catalog.sql
@<directory>\catproc.sql
@<directory>\catexp.sql

Ok, the database is configured. Change the SYSTEM and SYS passwords, to ensure better security for your database.

or if you still have confusion just log on to www.learn-Oracle-OLAP.com to get ur issue resolved. creating datab
ase through dbc assistsnce is easy but creating
manually is bit tuf because u need to type the
code on to word pad and then execute it. even i faced
the same problem i consulted e-book available at
www.learn-Oracle-OLAP.com and got the solution.

This post has been deleted.

Mon, 12 Oct 2009 03:02:47 -0700

This post has been deleted.

Mon, 12 Oct 2009 03:01:05 -0700

This post has been deleted.

Error in connecting Oracle 11g from MS Access

Thu, 10 Sep 2009 00:59:13 -0700

But it gives me an error 'Reserved error (-7778); there is not message for this error'. Also, when i changed connection parameter (eg, uid or pwd etc), it will give me odbc error in connection. Does it mean that Oracle connection is done succesfuly, but there is any problem in fetching data from Oracle?

I have install 11g enterprise edition on my system winxp; and install ODBC and OLEDB providers with that.

I can run such query with Oracle 9i.

Did i do any mistake with Oracle 11g installation?

Please help.

A tricky Left-outer Join :-

Wed, 02 Sep 2009 22:03:09 -0700

Hello SQL Pros,

I'm relatively new to Oracle / SQL programming & I have this requirement wherein a Table (named "Dealsummary") have to be Left-Outer joined with a set of other tables (Eg : "Riskrun") based on certain set of conditions.

The issue is that; in addition to the field (in "Dealsummary" table) using which the left-outer join has to be done, there's a field from the other table which should also be put into the WHERE clause; which when done & the query executed; looks like i'm given an Inner join result rather than a Left-outer join result. The essential part of the existing query is given below :

SELECT A.TradeID, B.PV // PV can be NULL or NOT NULL
FROM DEALSUMMARY A, RISKRUN B
WHERE A.TradeID = B.TradeID(+)
AND B.RunID = (SELECT MAX(C.RunID) FROM RISKRUN C
WHERE C.TradeID = A.TradeID)

Note : The RISKRUN table contains the fields; RunID, TradeID & PV.

To clarify more, This table ;
-> contains Many rows with many RunIDs for a single TradeID (hence selecting the latest RunID from using a subquery in the above SQL).
-> need not possess any rows / RunIDs for certain TradeIDs found from DEALSUMMARY table. Even then, A.TradeID values should be displayed with NULLs in B.PV forming a Left-Outer Join.

Can any of you please help me out in fixing this up & produce a left-outer join from this query maitaining all the conditions ?
I believe this is relatively simple for the experienced SQL programmers here.

Thankyou very much in advance for the help !

Re: oc4j-island:default_island, ajp-host:-missing-, ajp-port:-missing-

Tue, 11 Aug 2009 23:32:31 -0700

Search for ajp in the opmn.XML file and increase the port range to 200. Make sure not to overlap with the rmi and the other one. Can't remember the name. Do this every where u find ajp.
Validate and reload the file
opmnctl validate
opmnctl reload
stop and star

this is something to try. Some of your error messages I've seen when my ports are taken by another app and my oc4j instances don't come up.

Re: oc4j-island:default_island, ajp-host:-missing-, ajp-port:-missing-

Sun, 09 Aug 2009 08:23:25 -0700

Hi:

Thank you for your help!!
I also did comparison of the original and new ias.properties and the opmn.xml file and it shows no change.
Can you please tell me where the files are related to the
process_type `home` and `oc4j_bi_forms` so maybe I can check the port number use by them.

Thank You.
Yours,

Frustrated.

Re: oc4j-island:default_island, ajp-host:-missing-, ajp-port:-missing-

Sat, 08 Aug 2009 23:30:21 -0700

Re: oc4j-island:default_island, ajp-host:-missing-, ajp-port:-missing-

Sat, 08 Aug 2009 23:08:22 -0700

Re: oc4j-island:default_island, ajp-host:-missing-, ajp-port:-missing-

Sat, 08 Aug 2009 22:24:04 -0700

Hi:

Thank you for your help!!!!
Really appreciated it!!!!
I am attaching my ias.properties file.
I know I am asking a lot of questions but I am very new to oracle report.

Is there a way to start and stop the oc4j server in C:\OraHome_3\j2ee\home

I donot see any bin directory in home.
Normal standalone oc4j server has bin directory that contains the startup and shutdown command file.

Are there any more files you need to determine what the problem is?

Thank You.

Yours,

Frustrated

Attachment: ias.zip (732 bytes | downloaded 109 times)