SELECT WHERE pattern

需求：
字段A只有4列的取出来
解决方案： OPEN-SQL得pattern
SELECT *
  FROM   TABLE
  INTO     TABLE AAA
   WHERE A LIKE '____' .  四个_
_代表任意一个字符 %代表任意字符串

顺便zt一个sql优化指南：
Rules for Better SELECTS
All experienced ABAP programmers keep a small rule set in the back of their minds that helps them write efficient queries. Whenever they come upon a SELECT statement, they apply these rules. As a member of the SAP ABAP development team, I’ve been gathering rules for years. Here’s a glance at my set (for simplicity, all database tables and nearly all examples are taken from the ABAP syntax documentation).

Rule 1: Keep the hit list small.
The hit list is the set of rows determined by the WHERE clause. In other words, it is the set of table rows that the database system must take into account to respond to the query. Don’t confuse the hit list with the solution set, which is the set of rows (not necessarily from the table the query deals with) the database system returns to the program. Notice that in many cases the query’s hit list and the solution set are different.
What is the reason behind this rule? A smaller hit list saves disk I/Os by the database system and often reduces network load. Both of these factors have a strong influence on your program’s runtime performance.
How can you use ABAP to limit a hit list? First, use a WHERE clause whenever possible. Second, always try to describe the full search condition (not just part of it) within a single WHERE clause. ABAP provides a variety of arithmetical and logical operators to do this. Compared with your application program, the database server is usually the better place to evaluate search conditions.
Let me give you an example: Run a query against the flight table to produce a list of all flight dates for Lufthansa flight 0300 in 1998. Air carrier and air connection are known and can easily be used within the WHERE clause of the SELECT statement. The relevant year is also given, but at first glance it is difficult to build an appropriate search condition. Faced with this situation, you could be tempted to use the ABAP CHECK statement.
CHECK evaluates the subsequent logical expression. If it is true, the processing continues with the next statement. CHECK with a negative outcome terminates the current SELECT-ENDSELECT loop to start with the next row of the solution set, if there is one. Considering that fldateis a type D(YYYYMMDD) column of the sflighttable, a correct fragment of an ABAP program could look like this:
SELECT fldate
FROM sflight INTO xdate
WHERE carrid = 'LH'
AND connid = '0300'.
CHECK xdate = '1998'.
WRITE: / xdate.
ENDSELECT.
With ABAP in general, CHECK does the job of pattern matching in character variables. But in the WHERE clause of a SELECT statement, pattern matching also can be done easily by applying the LIKE operator. Within the search pattern, two characters have a particular meaning: “_” stands for any one character and “%” stands for any character string. With this in mind, a better solution would be:
SELECT fldate
FROM sflight INTO xdate
WHERE carrid ='LH'
AND connid ='0300'
AND fldate LIKE '1998%'.
WRITE: / xdate.
ENDSELECT.

Rule 2: Minimize the amount of data transferred between the database and the application (the solution set).
Following this rule reduces your network load. How do you do this with ABAP? First, notice that the solution set is mostly described by the SELECT clause; therefore, use a field list instead of SELECT * whenever possible. Second, use the aggregate functions COUNT, MAX, MIN, SUM, and AVG. These functions take on values that depend on all the rows selected and return information about rows, not the rows themselves. When you apply aggregate functions, the calculation takes place in the database server, and only the result (one value) is transported to the application server. For example, take the following query that returns the total number of passengers in the year 1998:
sum = 0.
SELECT seatsocc
FROM sflight INTO xseatsocc
WHERE fldate LIKE '1998%'.
sum = sum + xseatsocc.
ENDSELECT.
WRITE: / sum.
Use instead:
SELECT sum( seatsocc )
FROM sflight INTO sum
WHERE fldate LIKE '1998%'.
WRITE: / sum.
Last but not least, apply UP TO n ROWS if you are only interested in a top-n solution set. The benefit is obvious if n is a small integer because the database server and the application server always communicate in packages up to 32KB in length (depending on the network/communication hardware). If your request includes no information about the restriction, the database server has to transfer unneeded data. Take, for example, this query against the customer table scustom, which outputs a list of 10 business customers receiving the greatest discounts:
SELECT id name discount
FROM scustom
INTO (xid, xname, xdiscount)
WHERE custtype = 'B'
ORDER BY discount.
IF sy-dbcnt > 10. EXIT. ENDIF.
WRITE: / xid, xname, xdiscount.
ENDSELECT.
Instead of that approach, use:
SELECT id name discount
FROM scustom UP TO 10 ROWS
INTO (xid, xname, xdiscount)
WHERE custtype = 'B'
ORDER BY discount. WRITE: / xid, xname, xdiscount.
ENDSELECT.

Rule 3: Keep the number of round trips between the database and the application small.
If you can handle a query with a single SELECT, don’t use more. Why not? Every invocation of the database system consumes a certain amount of time, which can add up to a continuously growing overhead.
For example, look at this program fragment that lists all not-sold-out flights from Frankfurt to New York between September 10 and 20. The flight schedule (including cities of departure and arrival) is stored in the spfli table, and the corresponding flights (including flight date, capacity, and utilization of capacity) can be found in the sflight table.
SELECT carrid connid
INTO (xcarrid, xconnid)
FROM spfli
WHERE cityfrom = 'Frankfurt'
AND cityto = 'New York'.
SELECT carrid connid fldate
INTO (ycarrid, yconnid, ydate)
FROM sflight
WHERE carrid = xcarrid
AND connid = xconnid
AND fldate BETWEEN '19990910' AND '19990920'
AND seatsocc < seatsmax.
WRITE: / ycarrid, yconnid, ydate.
ENDSELECT.
ENDSELECT.
This is an example of a multi-way SELECT. Using multi-way SELECTs reduces performance, particularly when there are large amounts of data in the outer table. The inner SELECT statement is processed for every outer SELECT loop.
But there is a better way. In a relational data structure, it is quite normal for data that belongs together to be split up across several tables. This design method is called normalization. To regroup the information in a database query, you can link tables using a JOIN command, which helps avoid nested SELECT-ENDSELECT loops. A thorough introduction to joins is beyond the scope of this column; simply put, the JOIN command specifies conditions for the columns of the tables involved. An (inner) join contains all combinations of rows from the first table with the rows of the second table that meet the condition specified in the ON clause. In this example, the rows of the flight schedule and the flight table that have identical air carrier and air connection identity are combined.
Combining those rows gives you a single SELECT, which in addition causes less data to be transferred from the database to the application:

SELECT f~carrid f~connid f~fldate
INTO (ycarrid, yconnid, ydate)
FROM sflight AS f INNER JOIN spfli AS p
ON f~carrid = p~carrid
AND f~connid = p~connid
WHERE p~cityfrom = 'Frankfurt'
AND p~cityto = 'New York'
AND f~fldate BETWEEN '19990910' AND '19990920'
AND f~seatsocc < f~seatsmax.
WRITE: / ycarrid, yconnid, ydate.
ENDSELECT.
An even more sophisticated way to obey this rule is to use subqueries (queries within queries). Here’s an example using a subquery that returns the day of departure with the most passengers for all air connections and all carriers:
SELECT f~carrid f~connid f~fldate
FROM sflight AS f INTO (xcarrid, xconnid, xdate)
WHERE seatsocc =
(SELECT MAX( seatsocc ) FROM sflight
WHERE carrid = f~carrid AND connid = f~connid).
WRITE: / xcarrid, xconnid, xdate.
ENDSELECT.

Rule 4: Keep the cost of the search down.
In order to serve your requests quickly, the database system needs your help. Because scanning through all of a table’s data pages can be too expensive, the database system lets you define one or more indexes to keep search time down. An index is arranged as a hierarchy of pages; at the bottom level, a sequence of leaf pages contains the index key values of the table rows, each with a pointer to the location of the row containing that value. The keys in the leaf pages are in key sequence. At a higher level, a page lists the highest key value to be found in each page at the lower level. The topmost level is a single root page from which any other page of the index can be found. An index lookup works down from the root page to a leaf page. The root page and higher level pages are almost always found in a page buffer because they are used so often. The odds of finding a leaf page in a buffer depend on the size of the index; the odds become poorer as the size of the table increases. Although it can be costly, this kind of search is still a bargain compared with the alternative: performing each lookup by reading the entire table (“table scan”).
An index can be constructed across several fields of the table (in particular non-key fields), and a table can have several indexes. The primary index is always created and administered by the R/3 System across all key table fields. All other indexes are called secondary indexes and must be created separately.
The database system’s optimizer component decides how to perform a query. Its most important job is to determine the most efficient way to access each table — by a sequential scan of its rows or by an existing index. The total search cost is determined by the number of searches, which in turn is determined by what indexes are available and how the database optimizer uses them. You should specify the WHERE clause to keep the number of searches low and create suitable indexes if necessary.
How can you specify proper WHERE clauses in critical queries? First, use as many EQ operators as possible for the maximum likelihood that the optimizer understands what you want. But notice that this demand is in some contrast to rule 1 (because complete WHERE conditions may include operators other than EQ).
Second, make sure that, within the WHERE clause, the first n fields of the designated index are stated with EQ. A field in an index can then only be used if all the fields in the index line before it are stated with EQ. If this is the case, a query that uses the first n fields of an index can be executed very quickly. Note: The sequence of the fields in the WHERE clause is not important, although in the index their sequence is significant.
The following query, an example of good programming practice, lists all booking identifiers for the Lufthansa flight 0300 on September 10. Because the fields specified in the WHERE clause with EQ are the first three key fields of the table SBOOK, the statement can be processed very quickly by evaluating the primary index.
SELECT bookid
FROM sbook INTO xbookid
WHERE carrid = 'LH'
AND connid = '0300'
AND fldate = '19980910'.
WRITE: / xbookid.
ENDSELECT.

Rule 5: Remove the load from the database.
In the R/3 server architecture, there is only one database server but any number of application servers. Although you cannot add extra database servers to your R/3 System, you can add more application servers to improve scalability. Therefore, you’ll want to remove any unnecessary load on your database. Here are some ideas to achieve this:
• Avoid repeated reading of the same data.
• Check whether a SELECT is really needed before an UPDATE is made.
• Decide whether the data is needed in a certain order? If so, does the desired sorting correspond to the index used? If not, use the ABAP SORT statement instead of the ORDERBY clause of SELECT. This moves load from the database to the application servers.
• Find out about “table buffering” on the R/3 application server in the R/3 library (use the Help Menu from the main menu).
Special Cases
Some of the rules I’ve detailed unveil their benefits only if you use tables of a certain minimum capacity. For example, a SELECT statement without a WHERE clause usually implies a design error in the program, especially if the SELECT refers to an SAP table that usually grows quickly (for example, BKPF, BSEG, COBK, COEP, LIPK, MKPF, VBAK, or VBAP). Queries with missing WHERE clauses referring to such tables may perform well at the beginning of production but quickly increase their runtime because data is added daily and often in large quantities.
Take my recommendations as rules of thumb rather than laws. Some of the goals of the rules are even inconsistent. For example, in many cases the use of a sophisticated WHERE clause confuses each SQL optimizer. As a result, the optimizer chooses “table scan” as its search strategy, completely reversing the programmer’s intentions.
Due to this potential dilemma, I would like to introduce the most important rule:
Rule 0: Think and experiment!
If you want a closer look, the R/3 System provides the SQL trace. Here you can find detailed information about elapsed time or the optimizer’s execution plan for every executed SQL statement.
The recommendations in this column generally hold true for all SAP-supported database systems. All relational database systems are constructed on a single mathematical theory, which is why most search algorithms behave similarly on all database systems. Considerable differences are only found in their optimizer strategies.