Skip to Main Content


Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, TomBoy.

Asked: April 22, 2002 - 9:23 am UTC

Answered by: Tom Kyte - Last updated: February 11, 2020 - 3:51 am UTC

Category: Developer - Version: 8.1.7

Viewed 1000+ times

You Asked

Hello Sir :

I know that , i don't have rights to ask this questions to you.But still i am trying. Sir ...Can u please explain me the Architecture of your site. It means the Work-Flow , how u are getting different questions from various people , how u are giving the reply & storing all question on site as a Archivival & Some under HOT ARTICLE & some under Most Popular.

Your application using which technology as a FRONT-END. what Application/Web Server the site is using.

Sir .. Please provide the detailed description of same , if possible.

Thaks a lot for ur Time.

Warm Regadrs
- TomBoy.

and we said...

</code> <code>

covers most of that.

The "work flow" piece is very simple.

I control the max number of submitted but never read questions (usually 10). This is done via a trigger. I have a flag in my questions table that tells me:

o not answered
o read/more info supplied
o read/answered -- do not publish
o read/answered -- published
o read/awaiting more info
o deferred

Questions in the first two categories are the ones I'm interested in -- they are the ones that I have to answer. The others are what I display on the website (when published).

Hot article/most popular -- those are just queries. when you read a document, we increment a counter on it. So, finding the recently hot items and the overall most popular is a simple sort.

Archival -- again, that's easy. When I touch an article (update it), the timestamp is updated. Simple query.

and you rated our response

  (30 ratings)

Is this answer out of date? If it is, please let us know via a Review


Search in asktom

August 26, 2002 - 1:33 am UTC

Reviewer: Irfan from Kuwait

Hi Tom,
Recently that if we search in your site;
The number of records found does not match the records displayed. For example if I do a search for dbms_flashback, the header says, Approximately 17 records found, but only 3 questions are displayed.


Tom Kyte


August 26, 2002 - 7:50 am UTC

It has always said approximately, just like google says "Results 1 - 10 of about 6,510,000. " when you search for Oracle.

It goes right along with my belief that you should NEVER do a count(*) before running a query to tell the user how many rows will result (that is an utter waste of time). We approximate and only give a page forward (notice how google lets you access the first 10 pages only -- there is no "last page", it would take google FOREVER to get to the page with hit 6,000,000 on it!)

Also, on google, sometimes it says "showing 1-10 of about 100" and if you click on page ten it says "whoops -- we'll just drop you off at page 7 as we've just discovered there are really only 72 hits!"


If you want see this effect on google, run this link: </code> <code>
it should say (as of Aug 26 2002 at 8am EDT anyway) results 1-10 of about 47. Now, click on page 4 and see what happens (you'll be on page three looking at results 21-23, there are only 23)

Sentence search

August 26, 2002 - 8:30 am UTC

Reviewer: Martin from Amsterdam, NL


Is it Possible to search for an exact sentence on your site?
Like you would search for "exact sentence" (quoted string) on google? It doesn't seem to work that way on your site, or does it?



Tom Kyte


August 26, 2002 - 10:36 am UTC

Your wish has been granted. It is goole-esque.

"online operations"

will find only things with that phrase

online operations

will find all with online AND operations somewhere.

You can mix them up, eg:

this is a test of "the emergency broadcast system" had this been a real test

works as expected (well, as I expect it to work ;)

Interested in the software behind ASKTOM, the stuff used to develop the screens? Well, it's going "public". Stop by:
</code> <code>

the Oracle Technical Network (OTN) and read about it (click on Project Marvel and then "Tell me more" or "Quick Tour"

problem with project Marvel link

August 26, 2002 - 4:13 pm UTC

Reviewer: Doiug from CT, USA

Tom.. when I try to click on that project marvel link I get asked for authentication to "FORMS" on the asktom site.
I've tried it in IE and Mozilla.

Tom Kyte


August 26, 2002 - 7:22 pm UTC

WHOOPS, fixed that. sorry.

sorry.. I mean FLOWS from asktom

August 26, 2002 - 4:14 pm UTC

Reviewer: Doug from CT, USA

Thanks for fixing the search function

August 27, 2002 - 2:46 am UTC

Reviewer: Martin from Amsterdam, NL

Thanks Tom,

It's good to see even you make coding errors sometimes ;)

Tom Kyte


August 27, 2002 - 8:30 am UTC

Hey now, that was no coding error - it was the intended behaviour :)

What you proposed is known as an "enhancement request". I changed the formatting of the Oracle Text query. Before when you entered:

"how now" brown cow

we stripped out lots of stuff (special characters), turning that into

how now brow cow

and then

{how} and {now} and {brown} and {cow}

I enhanced the routine to look for balanced quotes and upon finding them -- treat that as the word. So, now the query is

{how now} and {brown} and {cow}

Estimating the number of query results....

August 27, 2002 - 9:00 am UTC

Reviewer: Mark J. Bobak from Dearborn Heights, MI

Hi Tom,

When you estimate or approximate the number of query
results, how do you do that?

What coding mechanism or technique do you use to do that?


Tom Kyte


August 27, 2002 - 9:24 am UTC

I use ctx_query.count_hits


August 27, 2002 - 9:31 am UTC

Reviewer: Martin from Amsterdam, NL

Sorry to have doubted your coding, Tom ;)

Keep up the good work, I really enjoy reading your site and soon i'll be enjoying your book as well

approximate results

August 27, 2002 - 11:39 am UTC

Reviewer: Dan from NY

You wrote: "It goes right along with my belief that you should NEVER do a count(*) before running a query to tell the user how many rows will result (that is an utter waste of time). We approximate and only give a page forward..."

This is an intruiging statement to me. Tom, how do you apply this to a typical select statement in a typical web application? That is, how do you determine the size of the result set rows in order to let users page through results if not by a count(*)? (I'm guessing intermedia and google both have additional data structures that allow them to efficiently over-estimate the number of results.)

Tom Kyte


August 27, 2002 - 1:23 pm UTC

I don't -- goto my search page and you'll see.

goto that google link above -- they don't either (they "guess" and when wrong, don't really admit it -- just sort of "fix it up" on the fly)

intermedia uses its index to get a guesstimate and google most likely has their own algorithm.

In most cases, I give you simply this information:

a) your search ran
b) here are the first 10 hits
c) if there are more then 10 hits (eg: that 11th fetch that I didn't print returned with data) I'll give you a NEXT button

And that is all. I don't tell them "hey you got N records". Sometimes I even do this:

select *
from ( THEIR QUERY HERE and rownum <= 500 )
order by whatever;

just to limit it to 500 hits (knowing full well that NO one is going to hit page down 50 times and if they do -- too bad for them -- their search was not selective enough)

ignore last

August 27, 2002 - 11:41 am UTC

Reviewer: Dan from NY

duh... missed the answer above.

Sentence search

August 28, 2002 - 12:08 pm UTC

Reviewer: Paul from UK


I like you enhancement a lot - I was using it to look for "function-based indexes", in fact.

However, I notice that it's not quite perfect.

Searching for <"how now" brown cow> just seems to do a search for {how now}, though <"how now" brown "cow"> works fine. It seems to be ignoring any non-quote-enclosed strings after the last occurrence of a quote-enclosed string, or something like that.

It's interesting to see how many entries include the word "cow" - this site must be used by the farming community a lot :-)

Tom Kyte


August 28, 2002 - 12:26 pm UTC

He he, amazing what a difference ONE line of code can make sometimes ;)

It is nearer to perfection now...


September 10, 2002 - 11:08 pm UTC

Reviewer: Fenng from China

hi,Tom ,I know there is a site that called "askus" ,
very similar to asktom ,
I want to know what is the relation about the 2 site?

Tom Kyte


September 11, 2002 - 7:38 am UTC

we run a couple copies of this -- for example is another "askus" piece of software (same application, different guy answering the questions)

I mean here:

September 10, 2002 - 11:14 pm UTC

Reviewer: Fenng from china

Tom Kyte


September 11, 2002 - 7:39 am UTC

That is help for the Project Marvel piece of software which is what was used to write askus (aka asktom)

query on asktom

January 16, 2003 - 11:16 am UTC

Reviewer: mo


When I search on your site for "CURSOR" it will tell me there are 1132 records found and display two pages with about 30 -40 records.

None of those had the article I was looking for labeled "CURSOR". I had to go to the weekly list to get to it. I think maybe something is not right with search utility here.


Tom Kyte


January 16, 2003 - 11:25 am UTC

if you would like to understand the algorithm used:

</code> <code>

it is not a simple "search the subject" -- it is a function of how often things are referenced in the document. "cursor" -- very generic. goto google and search for "cursor" -- over 3MILLION hits. It is a tad vague -don't you think?

Amazingly -- due to the sheer NUMBER of comments you made -- a search for

cursor mo

turns it right up.... You need to be a tad more specific. search isn't "broken", search terms were way too broad.


January 16, 2003 - 11:43 am UTC

Reviewer: Christophe Parmentier from France

Nice to learn your site works.

Is this a pl/sql generated webpage like i've read in the Oracle Doc? I had doubt regarding performances but il looks pretty fast ;)

Tom Kyte


January 16, 2003 - 12:03 pm UTC

all of it is plsql, yes.

Still more questions!

January 17, 2003 - 2:38 am UTC

Reviewer: Christophe Parmentier from France

Wow, i may get a look at these plsql webpages then ;)

FMI, could we know on what's your Oracle Server running to get these performances with such a big DB ?


Tom Kyte


January 17, 2003 - 9:21 am UTC

search for

Interested in the software behind ASKTOM

on this page to see the tool (marvel) we used.

</code> <code>
describes the hardware environment and what else is running on here.

Top 5 Search Terms

January 26, 2003 - 7:23 am UTC

Reviewer: Jerry from Washington, DC USA

Any chance that you'll consider publishing something like the "Top 5" search terms, either cumulatively, or for a weekly period?

I am curious as to what people are working on or confused about.

I remember you published them once and I found it very interesting.

Tom Kyte


January 26, 2003 - 9:18 am UTC

always more to do, i'll put it in the queue ;)

URL not found

February 09, 2003 - 1:34 pm UTC

Reviewer: A reader

Hi Tom

I have marked following url adress of your useful site:

</code>, <code>

This link is not found. Could you get me the new link for this topic.
Best Regards.

Tom Kyte


February 09, 2003 - 3:27 pm UTC

In the future use the "book mark this page" link to bookmark.

but for now, just remove the sessionid from the URL -- 2013863, it'll start a new session for you.

</code>, <code>

Many thanks Tom

February 10, 2003 - 3:17 am UTC

Reviewer: A reader

Bug in search feature?

February 24, 2003 - 5:17 am UTC

Reviewer: Martin from The netherlands


Something might be wrong with your search feature. When searching for "The New Ask Tom" which is an existing question title nothing is found. I can still see it under the your questions-tab and according to that list it should be published?

Tom Kyte


February 24, 2003 - 9:56 am UTC

got it -- thanks for the example, its fixed now.

While your at it....

February 24, 2003 - 2:00 pm UTC

Reviewer: Jonas from Berkeley, CA

Can you please fix the back button after you click through to an article from a search or frontpage? The previous page expires almost immediately. By the time read the article, you have to click back, click refresh, then click a dialogue button to repost the data. Its a tad painful :)

Tom Kyte


February 24, 2003 - 2:12 pm UTC

seems to me that would be a function of your browser?

I use mozilla, I've never had this happen.

I just tried IE 50, cannot get it to happen.


is it happening to others?
if so, what browser are you using and what are your cache settings?

brower type and cache settings

February 24, 2003 - 8:23 pm UTC

Reviewer: Jonas

IE 6 on XP. cache lookup is set to "automatic" with plenty of disk.

Tested in Mozilla and it worked fine.

9ias' database connection pooling

May 11, 2003 - 11:36 am UTC

Reviewer: Sean from Atlanta

I understood that asktom using the 9ias. I am learning the 9ias r2 architecture. There is data-sources.xml in the j2ee home, which is for config of the database connectivity. How do you manage database sessions? If you use OC4J, how do you set the parameters?:
max-connections, min-connections, and inactivity-timeout.
If you do not use J2EE, then what's your database connection pooling scheme? I assume you are not using the dedicated server.


Tom Kyte


May 11, 2003 - 12:53 pm UTC

I use plsql, short of a java procedure to send emails using the java mail api (and that happens in the background via dbms_job), there is no java involved in this site.

we use mod_plsql exclusively. It scales well beyond my needs, is simple to use, have very few "moving parts" that need to be coordinated to accomplish something.


May 11, 2003 - 3:19 pm UTC

Reviewer: Sean from Atlanta

Thanks for the answer.
A question might not be intellege, but I have had it in my mind for a while.
Oracle is big on OC4J for 9ias R2. For you, what knids of applications you may want to use a lot of MOD_OC4J and what are they you may rather use a lot of MOD_PLSQL. Do decissions base on the web functionalities, or performance, such users populations?

Tom Kyte


May 11, 2003 - 4:16 pm UTC

I've written one "big" application in Java, it was a palm sync conduit. I choose java because the other choice was C++ and Java in this case just made more sense. (even then, the java palm conduit pretty much just inserted into a global temporary table the changes from the palm and then a plsql stored procedure actually did the work -- since it was all about data, data in the database -- and sent the changes back to the palm for java to apply locally)

Everything else I write is in PLSQL and if PLSQL isn't capable of doing it I might do a small java stored procedure. If that cannot do it, a little C covers it.

So, all of my applications are written in PLSQL.

Oracle is big on OC4J for 9iAS for those people who have choosen java as their predominant application development language. If you picked a language -- java -- we feel our j2ee contains are outstanding (vs a BEA or IBM solution for example).

Many people have choosen the language and j2ee standard as their way of implementing applications. For those people, mod_oc4j is the way to go. there is mod_perl, php, plsql, etc etc etc etc -- and they are all valid and workable solutions as well.

Marvel Engine, what is it ?

May 19, 2003 - 3:49 pm UTC

Reviewer: Robert from PA

>>we use mod_plsql exclusively.

I took the Project Marvel "QuickTour" -
Is the "Marvel Engine" composed of a bunch of pl/sql packages ?


For the most part

May 22, 2003 - 11:52 pm UTC

Reviewer: Tyler D. Muth from Reston, VA

Thes are the objects that make up Marvel:

flows_010306@lap> select object_type,count(object_type) cnt
2 from user_objects
3 group by object_type;

------------------ ----------
LOB 84

13 rows selected.

COUNT_HITS - but what about other criteria

September 10, 2003 - 12:13 pm UTC

Reviewer: Tom Best from PA

With regard to the COUNT_HITS recommendation for estimating number of hits, what if you have additional criteria e.g.

where a = 10 and contains(idx,'string to find') > 0

Do I have to go back to using count(*) for this? It seems to me there is no other way.


Tom Kyte


September 10, 2003 - 8:03 pm UTC

i just say "approximately" and let them shoot me if I'm wrong.

I dont' think its a big deal. I've never done it. I won't. I take it out of every application I get access to. It is the worst thing to do.

Display by Topic

September 11, 2003 - 1:34 am UTC

Reviewer: Mohan from Bangalore

Hi Tom,

It will be better if you can add one more flag for each question. The name of the flag is "topic" or "category". each question can be a memmber of one or more categories.

backup&recovery->export, exp
backup&recovery->import, imp
backup&recovery->rman, "recovery manager"
backup&recovery-> "online backup", "cold backup"
query->sql->Top N
query->sql->DML->"bulk insert"

The list can be more exhaustive. The advantage is that if is possible to display questions category and subcategory wise. Search using a key word may not fetch all questions.

suppose I want all questions related to export. There are questions where only exp is used instead of export. Also imp for import.


How to access project marvel?

September 30, 2003 - 2:17 am UTC

Reviewer: Jap Boon Churn from Malaysia

Hi Tom,

I followed one of the link you provide in this page to the project marvel. But i was directed to </code> <code>

How can i view the details on project marvel from here?

Please advise.

Tom Kyte


September 30, 2003 - 7:08 am UTC

project marvel is now "feature HTML DB" of the database.

see html db

asktom credentials

April 19, 2004 - 3:44 am UTC

Reviewer: bob from PA

Saw this in a job ad on Monster:

"Experience in the following areas is an asset:

Familiarity with and Tom Kyte's books"

Too bad they can't even get the website address correct.

The new asktom website | AskTOM 4.3.11 Built with Oracle APEX

February 10, 2020 - 9:44 am UTC

Reviewer: Rajeshwaran, Jeyabal

First of all thanks to the new looks of asktom website.

one thing that i feel - that could be improved here is - while we scroll through some old pages /pages having huge follow ups - the top header (asktom logo, question/office hours/resources/ about link) occupies most of the screen contents all time.

can that be made like how jeff smith did it here - - ? when the vertical scroll bars are on the top edge- that jeff smith logo and other hyper links look big, but when you scroll down, they all become small and make room for more contents to fit.

Just an idea, let us know your thoughts and feasibility of this.

Connor McDonald


February 11, 2020 - 3:51 am UTC

I will ask the CSS guys about it