true

Find the best tutors and institutes for Oracle Training

Find Best Oracle Training

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

Oracle Training Updates

Ask a Question

Post a Lesson

All

All

Lessons

Discussion

Lesson Posted on 05 Jan IT Courses/SQL Programming IT Courses/Oracle Training IT Courses/PL/SQL +2 IT Courses/Oracle Training/Oracle PL/SQL IT Courses/Database Training less

Interview questions based on "level", a pseudocolumn

Gavi Yatnalli

It's going to be a multidimensional experience when you attend the classes. Take a chance and enjoy the ride.

1. Write a query to get the below output, 1 11 21 31 .. .. 91 2 12 22 32 .. .. 92 3 13 23 33 .. .. 93 .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. 10 20 30 40 100 Ans: select level,level+10,level+20,... read more

1. Write a query to get the below output,

1 11 21 31 .. .. 91
2 12 22 32 .. .. 92
3 13 23 33 .. .. 93
            ..
.. .. .. .. .. .. ..
.. .. .. .. .. .. ..
10 20 30 40     100

Ans:

select level,level+10,level+20,
         level+30,level+40,level+50,
         level+60,level+70,level+80,
         level+90
     from dual connect by level <=10;

 

2. Write a query to generate tables from 7 to 70.

Like,  7,14,21....8,16,24...9,18,27......70,140,210.

Ans:

Select a.a*b.b from
               (select level+6 a from dual connect by level <=64) a,
               (select level b from dual connect by level <=10)     b;

And there are many more.............

read less
Comments
Dislike Bookmark

Lesson Posted on 05 Jan IT Courses/PL/SQL IT Courses/Oracle Training/Oracle PL/SQL IT Courses/SQL Programming +2 IT Courses/Oracle Training IT Courses/Database Training less

How does a SQL Query Execute inside Oracle? What are the steps followed in the background?

Gavi Yatnalli

It's going to be a multidimensional experience when you attend the classes. Take a chance and enjoy the ride.

The above snapshot says it all. Hence it is said, 'Picture says it all'. Basically, there are 4-5 steps. There are two possible ways of query being executed. 1. Soft Parse- Improves performance. This is also called as 'Library Cache Hit'. 2. Hard Parse- Degrades performance. This is... read more

 

The above snapshot says it all. Hence it is said, 'Picture says it all'.

Basically, there are 4-5 steps. There are two possible ways of query being executed.
      1. Soft Parse-  Improves performance. This is also called as 'Library Cache Hit'.
      2. Hard Parse- Degrades performance. This is also called as 'Library Cache Miss'.

Let us understand the steps one by one briefly.
There is a parser inside Oracle, which parses the SQL Query for further processing.
The first 3 steps of SQL Query Execution are taken care of by the parser.

Step 1: Syntax check

       This checks only for syntax errors. A pure syntax check and nothing else.
Examples: 1. Are the keywords correct?
                      Like, 'seelect', 'form', 'groupby', 'orderby' are few common spelling mistakes.
                 2. Does SQL Query end with a semicolon (;)?
                 3. Are column names in the 'Select' Clause are separated by commas (,)?
                 4. Does 'Select' clause include only the colmuns which are present in the 'Group by' clause?
                 5. Does 'Group by' clause appear after 'Where clause'?
                 6. Is 'Order by' clause the last clause in the query?
                 etc. etc....

            In each of these cases, if the answer is 'No', oracles throws an error stating the same.

Step 2: Sematic chck

      Once the query passes the Syntax check, next step is Semantic check.
Here, the references of all objects present in the query are checked along with user privileges. The check happens against metadata maintained in the Oracle.
Examples: 1. Is table name valid or such a table exists?
                 2. Are columns names correct?
                 3. Does user have select/update/delete/insert/create privilege?
                 etc. etc. etc.

So during Syntax check, it doesn't check for the validity of table names, column names, privileges etc.

Let's say, I am running this... "select * from epm;" 

This passes Syntax check, though I am writing a wrong table name. Instead of "emp", I have written "epm". But, this query is fine syntax-wise and hence passes the Syntax check. But it fails in the next step of Semantic check where the object names are verified.

But we will not be able to notice whether a query failed at Syntax check or at Semantic check when we run a query from SQL*Plus or any other GUI. Because, everything is handled in one go from user point of view and the errors are sent if the Query fails at any step. Otherwise, we get the final output.

 Step 3: Shared pool check

This is an important check. I am planning to write a separte lesson on this. Let us understand briefly here.

Basically, once the query passed both Syntax check and Semantic check, it goes for third check called 'Shared pool check'

Just to put it briefly, 'Shared pool' is a memory segment present inside every Oracle instance, which retains the recently executed SQL and PLSQL code inside its 'Library Cache' based on LRU algorithm.

So, if parser finds that the query is present inside 'Library Cache' or 'Shared pool', then it is called 'Soft Parse'. It goes to step 5, executes the query and sends the output to whoever requested it.

As simple as that. But, if parser finds that such a query is not present inside 'Shared pool' or 'Library Cache', then it is called 'Hard Parse'. So, Oracle must carry out step 4 first to prepare the query and then go to step 5 finally to execute the query and then send the output to the requestor.

So, the first three steps are always carried out for every SQL Query and they are less expensive.

Step 4:

      The step 4 is very expensive or costly. Meaning, it consumes lot of resources to complete this, like CPU, I/O reads, physical reads, extra algorithms to generate mutliple queries and select the best one etc.

Hence, 'Hard Parse' degrades performance. We should keep this in mind always before writing any query. To put it simply for now, avoid hard coding and use bind variables to take the advantage of 'Soft Parse'.

Step 4 happens only in case of 'Hard parse', which is decided in step 3 as we discussed above.

We can split this into two sub-steps.
      a. Optimization: The Optimizer generates mutliple plans using various combinations of joins                      (Nested loop/Sort Merge/Hash), access paths (full table scan/index range scan/index skip scan                etc) and join orders.
          Finally selects the best plan based on cost. The lower the cost, the better the performance.

      b. Row source generation: Query execution plan is generated for the best query
          selected in the above step. This is what we see in Explain plan.
          We can further make modifications to the query, force the Optimizer to select an even lower cost            query by changing access paths, joins, join orders or using Hints. And we can verify if the                  execution plan got changed by again looking at its Explain plan.
         This is called 'Performance tuning' or 'Query Tuning'. Let's not go deep into it now.

None of this happens in case of 'Soft Parse' and hence improves performance.

Step 5: Query Execution

      Step 5 is again common for each query irresepctive of whether it is 'Soft Parse' or 'Hard Parse'. 

As we already discussed, it executes the SQL Query and sends the output
to the requested program or user.

 

So this is about it. To wrap up, there are two ways of execution or parsing namely- 'Hard parse' and 'Soft Parse'. And there are 5 steps totally. Steps 1 to 3 are common for every query or for each type of execution/parsing. Step 4 happens only for 'Hard Parse' way of execution and not for 'Soft Parse' way. Step 5 is the final and common step, which finally executes the SQL Query. Use Explain plan to check the execution plan selected by Optimizer and tune the query. Use Bind variables to enable 'Soft Parse'.

read less
Comments
Dislike Bookmark

Lesson Posted on 11/06/2019 IT Courses/Oracle Training/Oracle GoldenGate

GoldenGate Connectivity to Kafka

Ankit Goyal

Have around 9.2 years of I.T. industry experience. Have rich experience in following Oracle Technologies. 1....

Oracle GoldenGate (OGG) connects to Apache/Confluent kafka using GoldenGate for Big Data Adapter. OGG uses the big data for kafka properties file to connect to kafka servers/cluster and stream the data in real time. All Inserts,Updates, Deletes are streamed as Inserts in Kafka Consumer. Big Data adapter... read more

Oracle GoldenGate (OGG) connects to Apache/Confluent kafka using GoldenGate for Big Data Adapter. OGG uses the big data for kafka properties file to connect to kafka servers/cluster and stream the data in real time.

All Inserts,Updates, Deletes are streamed as Inserts in Kafka Consumer. Big Data adapter is powerful enough to stream before and after images in case of an update.

You can download the OGG for big data software from Oracle e-delivery website. for more details on Big data connectors, do reach out to me.

Thanks,

Ankit

read less
Comments
Dislike Bookmark

Looking for Oracle Training

Find best Oracle Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 15/12/2018 IT Courses/Oracle Training

Sub Queries in SQL(Structured Query Language)

Phanindra Oruganti

The Sun Technologies Software Training Service was formed in the view of the ever growing demands of...

Writing the Query in one more Query is called Sub Query. In Oracle SQl Sub Queries are Diveded into two categories Simple Sub Queries Cor-related Sub Queries The difference between Simple Sub Queries and Cor-related Sub Queries is in case of Simple Sub Queries the outer side Query Expression... read more

Writing the Query in one more Query is called Sub Query.

In Oracle SQl Sub Queries are Diveded into two categories

  • Simple Sub Queries
  • Cor-related Sub Queries

The difference between Simple Sub Queries and Cor-related Sub Queries is in case of Simple Sub Queries the outer side Query Expression can compare with inner side query only once. In the case of Cor-related Sub Queries the outward side query expression can compare with internal side query number of times based on the condition which we have specified.

Simple Sub Queries are the following categories:

  • Sub Query Comparison Test
  • Sub Query Membership Test
  • Sub Query Quantified Comparison Test
  • Existance Test

Sub Query Comparison Test: In this case, the inner side query can return only one record while comparing we can use any relational operator

Sub Query Membership Test: In this case, the inner side query can return more than one record while comparing we can use 'in' operator and the outer side query expression can compare with any one of the values which are produced by the Sub Query.

Sub Query Quantified Comparison Test: In this case, the inner side query can return more than one record, and the outer side query expression can compare with all the values which are produced by the subquery while comparing we can use operators >any, <any, >all,

Existence Test: To check the existence of record which are satisfying primary key and foreign key relationship between two columns which are existing in the same table.

 

read less
Comments
Dislike Bookmark

Answered on 11/10/2018 IT Courses/Oracle Training/Oracle PL/SQL

I have around 8+ yrs of experience in Support on PLSQL. I wish to change my job. But feeling hard to... read more

I have around 8+ yrs of experience in Support on PLSQL. I wish to change my job. But feeling hard to crack interviews. Could any body provide me some material on PLSQL and performance tuning so that I can get through interviews?

read less

V.R. Naga Pawan Yallapragada

Professional Faculty with 22 Years of teaching experience

oracle Pl/SQL certification prep material is excellent.
Answers 1 Comments 1
Dislike Bookmark

Answered on 13/11/2018 IT Courses/Oracle Training IT Courses/IT Certifications/Oracle Certification IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCA +5 IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCM IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCP IT Courses/Oracle Enterprise Linux 6 IT Courses/Oracle Training/Oracle 11i Financial IT Courses/Oracle Training/Oracle 11i Supply Chain OM less

Which is the best training institute in Pune for Oracle DBA training?

Pranit Patil

There are many training institute in Pune for oracle DBA training, but you have to find a right and best among them. You have to consider the teaching staff, teaching quality, training method and mainly placement after training so overall looking at this factor you need to join the best <a href="https://crbtech.in/programmes/dba-training-programme/... read more

There are many training institute in Pune for oracle DBA training, but you have to find a right and best among them. You have to consider the teaching staff, teaching quality, training method and mainly placement after training so overall looking at this factor you need to join the best <a href="https://crbtech.in/programmes/dba-training-programme/ ">oracle dba training institute in Pune</a>

read less
Answers 1 Comments
Dislike Bookmark

Answered on 09/10/2018 IT Courses/Oracle Training IT Courses/IT Certifications/Oracle Certification IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCA +5 IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCM IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCP IT Courses/Oracle Enterprise Linux 6 IT Courses/Oracle Training/Oracle 11i Financial IT Courses/Oracle Training/Oracle 11i Supply Chain OM less

What are some good Oracle apps training institutes available for both functional and technical in Mumbai?

Neeraj Bhatia

27yrs experience Oracle Certified Cloud Fusion, EBS, ERP Professional

Dear Bivash, I am taking classes for Oracle Financial applicaion functional online, so there is no location constraints, but for technical you need to find anoher teacher. Another thing is that first of all you need to decided in which modules you want to be proficient, becuase Oracle Apps has more than... read more

Dear Bivash,

I am taking classes for Oracle Financial applicaion functional online, so there is no location constraints, but for technical you need to find anoher teacher.

Another thing is that first of all you need to decided in which modules you want to be proficient, becuase Oracle Apps has more than 150 modules, in finance we can cover only 5 modules, in similar, you can get some multilple modules in other streams like SCM, Procurement etc.

 

read less
Answers 1 Comments
Dislike Bookmark

Answered on 24/10/2018 IT Courses/IT Certifications/Oracle Certification IT Courses/Oracle Training IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCA +2 IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCM IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCP less

What is oracle SQL online training?

Aniket Rajendra Bhandare

Html crearion

In this session of sql u will learn all queries of sql how to insert add delete the table or column using sql queries its easy to learn online
Answers 1 Comments
Dislike Bookmark

Answered on 09/10/2018 IT Courses/IT Certifications/Oracle Certification IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCA IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCM +2 IT Courses/Oracle Training IT Courses/Oracle Training/Oracle 11i Financial less

What is the best way to pass the Oracle 1Z0-448 exam?

Sarika Bali

Java Expert

Take formal training first then start preparing for exam thru practice tests and write exam.
Answers 1 Comments
Dislike Bookmark

Looking for Oracle Training

Find best Oracle Training in your locality on UrbanPro.

FIND NOW

Answered on 15/10/2018 IT Courses/IT Certifications/Oracle Certification IT Courses/IT Certifications/Oracle Certification/Oracle DBA OCA IT Courses/Oracle Training +1 IT Courses/Oracle Training/Oracle 11i Financial less

What is the use of Oracle SOA Suite?

Narasimha M

Hello Hema, Oracle SOA makes it easy to rapidly assemble services into modular and flexible business applications. The Industry’s Most Complete and Unified Application Integration and SOA Solution Oracle SOA transforms complex application integration into agile and re-usable service-based connectivity... read more

Hello Hema,  Oracle SOA makes it easy to rapidly assemble services into modular and flexible business applications.

The Industry’s Most Complete and Unified Application Integration and SOA Solution

Oracle SOA transforms complex application integration into agile and re-usable service-based connectivity to speed time to market, respond faster to business requirements, and lower costs.

Please Go through This Bellow link.

https://www.oracle.com/middleware/technologies/soasuite.html

 

read less
Answers 1 Comments
Dislike Bookmark

About UrbanPro

UrbanPro.com helps you to connect with the best Oracle Training in India. Post Your Requirement today and get connected.

Overview

Questions 976

Lessons 38

Total Shares  

+ Follow 24,801 Followers

Related Topics

Top Contributors

Connect with Expert Tutors & Institutes for Oracle Training

x

Ask a Question

Please enter your Question

Please select a Tag

X

Looking for Oracle Training Classes?

Find best tutors for Oracle Training Classes by posting a requirement.

  • Post a learning requirement
  • Get customized responses
  • Compare and select the best

Looking for Oracle Training Classes?

Find best Oracle Training Classes in your locality on UrbanPro

Post your learning requirement

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more